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
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
解决方案 »
- sql 2005 exec sp_columns 求解
- 求SQL
- |zyciis| 如何删除表中的自增字段 脚本
- 没学过sql想请教个问题!我没有分了.但还是请前辈们解答.谢了!
- 基础问题,关于SQL操作记录查看
- sql script 可以单步调试了么?
- 求sql语句
- 把一个字段定义成text类型之后,它的长度就变成16了,16是什么含义呀?谢谢!
- 在线等!!为什么卸载sql server时,出现sqlagent正在运行,而服务中又找不到!·
- ~~~~~~~~~~关于竖合计数据的问题!!_======请大师指教!!!!!!==100分,放出!!解决立马给分!!!!
- 用SQL从服务器复制文件至客户端问题?请各位高手相助.
- 存储过程问题???
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.useridselect
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
(
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
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)
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)
USE pubs
SELECT AVG(DISTINCT price)
FROM titles
WHERE type = 'business'支持小龙(^_____^) V
如果是SQL Server,尽量先建立一些视图来简化你的sql语句吧!
至于以上各位高人的答案,我想验证也是要化一些时间的。
呵呵
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