超超超级难的sql问题,漂亮MM在线等候,谢谢各位大侠了........

[longshenwang]

SELECT b.price, c.zbxh, d.chinaname, d.chinaspec, e.unitname, f.brand, f.Produce_Factory,
f.Minpacking, f.zlcc, f.qtsm, f.Elucidation, h.Enterprisename
FROM dbo.drexpert_pb a LEFT OUTER JOIN
dbo.user_product b ON a.user_productid = b.id AND a.bid_id = b.bidid AND
a.bid_id = b.bidid LEFT OUTER JOIN
dbo.bid_product c ON b.bid_productID = c.id AND b.bidid = c.bidid LEFT OUTER JOIN
dbo.product_basic d ON c.product_basicid = d.id LEFT OUTER JOIN
dbo.product_qt f ON b.id = f.user_productid AND
b.id = f.user_productid LEFT OUTER JOIN
dbo.unit e ON d.unitid = e.id LEFT OUTER JOIN
dbo.EnterpriseBasicInfor h ON b.userid = h.userid
我想显示f.zlcc相同且a.user_product的值也相同重复次数>=3次的记录显示出来
且a.bid_id=27
这个条件怎么加

[wgsasd311]

SELECT b.price, c.zbxh, d.chinaname, d.chinaspec, e.unitname, f.brand, f.Produce_Factory,
f.Minpacking, f.zlcc, f.qtsm, f.Elucidation, h.Enterprisename
FROM dbo.drexpert_pb a LEFT OUTER JOIN
dbo.user_product b ON a.user_productid = b.id AND a.bid_id = b.bidid AND
a.bid_id = b.bidid LEFT OUTER JOIN
dbo.bid_product c ON b.bid_productID = c.id AND b.bidid = c.bidid LEFT OUTER JOIN
dbo.product_basic d ON c.product_basicid = d.id LEFT OUTER JOIN
dbo.product_qt f ON b.id = f.user_productid AND
b.id = f.user_productid LEFT OUTER JOIN
dbo.unit e ON d.unitid = e.id LEFT OUTER JOIN
dbo.EnterpriseBasicInfor h ON b.userid = h.userid
where a.bid_id=27 group by f.zlcc,a.auser_product having count(1)>=3

[子陌红尘]

SELECT
b.price,
c.zbxh,
d.chinaname,
d.chinaspec,
e.unitname,
f.brand,
f.Produce_Factory,
f.Minpacking,
f.zlcc,
f.qtsm,
f.Elucidation,
h.Enterprisename
into #T
FROM
dbo.drexpert_pb a
LEFT OUTER JOIN
dbo.user_product b
ON
a.user_productid = b.id AND a.bid_id = b.bidid AND a.bid_id = b.bidid
LEFT OUTER JOIN
dbo.bid_product c
ON
b.bid_productID = c.id AND b.bidid = c.bidid
LEFT OUTER JOIN
dbo.product_basic d
ON
c.product_basicid = d.id
LEFT OUTER JOIN
dbo.product_qt f
ON
b.id = f.user_productid AND b.id = f.user_productid
LEFT OUTER JOIN
dbo.unit e
ON
d.unitid = e.id
LEFT OUTER JOIN
dbo.EnterpriseBasicInfor h
ON
b.userid = h.userid

select
a.*
from
#T a
where
a.bid_id=27
and
(select count(*) from #T where zlcc=a.zlcc)>1
and
(select count(*) from #T where user_product=a.user_product)>=3

[xueguang]

楼上的肯定不能执行

[xueguang]

我说的是一楼的

[samfeng_2003]

select * from
(
SELECT b.price, c.zbxh, d.chinaname, d.chinaspec, e.unitname, f.brand, f.Produce_Factory,
f.Minpacking, f.zlcc, f.qtsm, f.Elucidation, h.Enterprisename
FROM dbo.drexpert_pb a LEFT OUTER JOIN
dbo.user_product b ON a.user_productid = b.id AND a.bid_id = b.bidid AND
a.bid_id = b.bidid LEFT OUTER JOIN
dbo.bid_product c ON b.bid_productID = c.id AND b.bidid = c.bidid LEFT OUTER JOIN
dbo.product_basic d ON c.product_basicid = d.id LEFT OUTER JOIN
dbo.product_qt f ON b.id = f.user_productid AND
b.id = f.user_productid LEFT OUTER JOIN
dbo.unit e ON d.unitid = e.id LEFT OUTER JOIN
dbo.EnterpriseBasicInfor h ON b.userid = h.userid
) a
where
(select count(1) from
(
SELECT b.price, c.zbxh, d.chinaname, d.chinaspec, e.unitname, f.brand, f.Produce_Factory,
f.Minpacking, f.zlcc, f.qtsm, f.Elucidation, h.Enterprisename
FROM dbo.drexpert_pb a LEFT OUTER JOIN
dbo.user_product b ON a.user_productid = b.id AND a.bid_id = b.bidid AND
a.bid_id = b.bidid LEFT OUTER JOIN
dbo.bid_product c ON b.bid_productID = c.id AND b.bidid = c.bidid LEFT OUTER JOIN
dbo.product_basic d ON c.product_basicid = d.id LEFT OUTER JOIN
dbo.product_qt f ON b.id = f.user_productid AND
b.id = f.user_productid LEFT OUTER JOIN
dbo.unit e ON d.unitid = e.id LEFT OUTER JOIN
dbo.EnterpriseBasicInfor h ON b.userid = h.userid
) b where a.zlcc=b.zlcc and a.user_product=b.user_product)>=3
and a.bid_id=27

[老本]

如果Access就惨点；
如果是SQL Server，尽量先建立一些视图来简化你的sql语句吧！
至于以上各位高人的答案，我想验证也是要化一些时间的。
呵呵

[wgsasd311]

SELECT b.price, c.zbxh, d.chinaname, d.chinaspec, e.unitname, f.brand, f.Produce_Factory,
f.Minpacking, f.zlcc, f.qtsm, f.Elucidation, h.Enterprisename
FROM dbo.drexpert_pb a LEFT OUTER JOIN
dbo.user_product b ON a.user_productid = b.id AND a.bid_id = b.bidid AND
a.bid_id = b.bidid LEFT OUTER JOIN
dbo.bid_product c ON b.bid_productID = c.id AND b.bidid = c.bidid LEFT OUTER JOIN
dbo.product_basic d ON c.product_basicid = d.id LEFT OUTER JOIN
dbo.product_qt f ON b.id = f.user_productid AND
b.id = f.user_productid LEFT OUTER JOIN
dbo.unit e ON d.unitid = e.id LEFT OUTER JOIN
dbo.EnterpriseBasicInfor h ON b.userid = h.userid
where a.bid_id=27 and f.zlcc+a.auser_product
(SELECT f.zlcc+a.auser_product
FROM dbo.drexpert_pb a LEFT OUTER JOIN
dbo.user_product b ON a.user_productid = b.id AND a.bid_id = b.bidid AND
a.bid_id = b.bidid LEFT OUTER JOIN
dbo.bid_product c ON b.bid_productID = c.id AND b.bidid = c.bidid LEFT OUTER JOIN
dbo.product_basic d ON c.product_basicid = d.id LEFT OUTER JOIN
dbo.product_qt f ON b.id = f.user_productid AND
b.id = f.user_productid LEFT OUTER JOIN
dbo.unit e ON d.unitid = e.id LEFT OUTER JOIN
dbo.EnterpriseBasicInfor h ON b.userid = h.userid
group by f.zlcc,a.auser_product having count(1)>=3)

[wgsasd311]

SELECT b.price, c.zbxh, d.chinaname, d.chinaspec, e.unitname, f.brand, f.Produce_Factory,
f.Minpacking, f.zlcc, f.qtsm, f.Elucidation, h.Enterprisename
FROM dbo.drexpert_pb a LEFT OUTER JOIN
dbo.user_product b ON a.user_productid = b.id AND a.bid_id = b.bidid AND
a.bid_id = b.bidid LEFT OUTER JOIN
dbo.bid_product c ON b.bid_productID = c.id AND b.bidid = c.bidid LEFT OUTER JOIN
dbo.product_basic d ON c.product_basicid = d.id LEFT OUTER JOIN
dbo.product_qt f ON b.id = f.user_productid AND
b.id = f.user_productid LEFT OUTER JOIN
dbo.unit e ON d.unitid = e.id LEFT OUTER JOIN
dbo.EnterpriseBasicInfor h ON b.userid = h.userid
where a.bid_id=27 and f.zlcc+a.auser_product in
(SELECT f.zlcc+a.auser_product
FROM dbo.drexpert_pb a LEFT OUTER JOIN
dbo.user_product b ON a.user_productid = b.id AND a.bid_id = b.bidid AND
a.bid_id = b.bidid LEFT OUTER JOIN
dbo.bid_product c ON b.bid_productID = c.id AND b.bidid = c.bidid LEFT OUTER JOIN
dbo.product_basic d ON c.product_basicid = d.id LEFT OUTER JOIN
dbo.product_qt f ON b.id = f.user_productid AND
b.id = f.user_productid LEFT OUTER JOIN
dbo.unit e ON d.unitid = e.id LEFT OUTER JOIN
dbo.EnterpriseBasicInfor h ON b.userid = h.userid
group by f.zlcc,a.auser_product having count(1)>=3)

[zhurenweile]

第一种我式了是不行

[zhurenweile]

wgsasd311(自强不息)查出来的记录是重复的呀，怎么过虑呀，第条同一条记录显示3次，怎么把这个过虑掉，只显示一次

[chenhuilin_chen]

你不会是超超超漂亮的MM吧,有QQ吗>留一个,我帮你解答.

[破法者]

支持美女。我是大龙

[破法者]

使用DISTINCT去掉重复的吧。


USE pubs
SELECT AVG(DISTINCT price)
FROM titles
WHERE type = 'business'

支持小龙(^_____^) V

[破法者]

顺便问下小龙，access是否支持join查询

[撸大湿]

看的眼花
：）

[vivianfdlpw]

SELECT
b.price,
c.zbxh,
d.chinaname,
d.chinaspec,
e.unitname,
f.brand,
f.Produce_Factory,
f.Minpacking,
f.zlcc,
f.qtsm,
f.Elucidation,
h.Enterprisename
into #T
FROM
dbo.drexpert_pb a
LEFT OUTER JOIN
dbo.user_product b
ON
a.user_productid = b.id AND a.bid_id = b.bidid AND a.bid_id = b.bidid
LEFT OUTER JOIN
dbo.bid_product c
ON
b.bid_productID = c.id AND b.bidid = c.bidid
LEFT OUTER JOIN
dbo.product_basic d
ON
c.product_basicid = d.id
LEFT OUTER JOIN
dbo.product_qt f
ON
b.id = f.user_productid AND b.id = f.user_productid
LEFT OUTER JOIN
dbo.unit e
ON
d.unitid = e.id
LEFT OUTER JOIN
dbo.EnterpriseBasicInfor h
ON
b.userid = h.userid

--查询
select
a.*
from
#T a
where
a.bid_id=27
and
(select count(*)
from #T
where zlcc=a.zlcc
and user_product=a.user_product
)>=3