g_trade_tradelist 是订单表
g_trade_goodslist 是物品明细表
两个表之间tradeid字段是关联的.
tradelist中tradestatus=5 and printexpress=''之外的单是不查询的.
现在想查询在g_trade_goodslist中出现次数最多的货品(货品列名是'goodsid')在g_trade_tradelist中对应的所有订单(满足tradestatus=5 and printexpress=''两个条件的)我用的是
select * FROM [wdgj30].[dbo].[G_Trade_GoodsList] where TradeID in(
select TradeID from (
select top 1 goodsid,count(GoodsID)as c from wdgj30.dbo.G_Trade_GoodsList where TradeID in(
select tradeid from wdgj30.dbo.g_trade_tradelist where tradestatus=5 and printexpress=''
)group by GoodsID order by c desc
)a
)
但是出来的结果好像不是我要的.
中间几行可以查出出现次数最多的goodsid,但是往回查g_trade_tradelist表的时候就把所有订单都查询到了....
g_trade_goodslist 是物品明细表
两个表之间tradeid字段是关联的.
tradelist中tradestatus=5 and printexpress=''之外的单是不查询的.
现在想查询在g_trade_goodslist中出现次数最多的货品(货品列名是'goodsid')在g_trade_tradelist中对应的所有订单(满足tradestatus=5 and printexpress=''两个条件的)我用的是
select * FROM [wdgj30].[dbo].[G_Trade_GoodsList] where TradeID in(
select TradeID from (
select top 1 goodsid,count(GoodsID)as c from wdgj30.dbo.G_Trade_GoodsList where TradeID in(
select tradeid from wdgj30.dbo.g_trade_tradelist where tradestatus=5 and printexpress=''
)group by GoodsID order by c desc
)a
)
但是出来的结果好像不是我要的.
中间几行可以查出出现次数最多的goodsid,但是往回查g_trade_tradelist表的时候就把所有订单都查询到了....
解决方案 »
- 谁有SQL Server 2000的ODBC驱动jar包?跪求!
- 如何设计DATABASE。让数据库表每月月底清空?
- 怎样修改<long text>的内容?
- window2003服务器上开启防火墙后远程不能链接数据数据库
- 导出问题
- 高手啊!帮我看看,急啊!!!
- 我公司規定應付款:過萬元就只結到百,千元以下的只結到十,如50,183.45實際付是50100.00;1,567.89實付1,560.00------請問有沒有對應的函
- MS-SQL2005的一个备份,拿到另一个电脑上还原为什么会出现这样的错误?急!!!!!
- 如何将xml导入sql,急~~~~~~~~~~~~~~~~~~~~~~~~~
- 怎样用procedure 做bom表的展开!急急!谢谢
- 讨教:如何实现列转换成行
- SQL sever 2000数据库出了表还能存别的么?
select top 1 goodsid,count(GoodsID)as c
你的第2行和第3行语句好像有错,第2行选择TradeID列,但是第3行里面只有goodsid与c,没有TradeID,这个语句能运行吗?
FROM g_trade_tradelist a
WHERE EXISTS ( SELECT 1
FROM ( SELECT TOP 1
tradeid ,
COUNT(1) c
FROM g_trade_goodslist
GROUP BY tradeid
ORDER BY COUNT(1)
) b
WHERE a.tradeid = b.tradeid )
AND tradestatus = 5
AND printexpress = ''
怎么跟我在网上看到的exists说明不一样呢...下面是引用,重点最后一句话
而我們打入的 SQL 是:SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE region_name = 'West')我們會得到以下的答案:SUM(Sales)
2750
乍看之下,這個答案似乎不太正確,因為內查詢有包含一個 [region_name = 'West']
的條件,可是最後的答案並沒有包含這個條件。實際上,這並沒有問題。在這個例子中,內
查詢產生了超過一筆的資料,所以 EXISTS 的條件成立,所以外查詢被執行。而外查詢本
身並沒有包含 [region_name = 'West'] 這個條件。
goodslist:
tradeid goodsid ...
100 b
100 b
tradeid goodsid
100 b
101 b
102 b
103 b
104 b
...這樣?
goodslist:
tradeid goodsid ...
100 b
101 d
100 c
103 d
100 d
105 d
tradelist:
tradeid tradestatus printexpress
100 5
101 11
102 5
103 5
104 5
105 5
106 11我要的结果是tradelist表中的tradeid=103,100,105
我感觉我这样说你会更乱....
if object_id('#g_trade_tradelist') is not null drop table #g_trade_tradelist
if object_id('#g_trade_goodslist') is not null drop table #g_trade_goodslist
create table #g_trade_tradelist(TradeID int identity(1,1) not null,tradestatus int null,printexpress nvarchar(100) null)create table #g_trade_goodslist(ID int identity(1,1) not null,GoodsID int null,TradeID int null)
insert into #g_trade_tradelist
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,''
insert into #g_trade_goodslist
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,5 union all
select 2,6 union all
select 2,7 union all
select 2,8 union all
select 3,9 union all
select 3,10 union all
select 3,10 union all
select 4,11 union all
select 4,11 union all
select 5,12 ;with cte as(
select top 1 max(goodsId) as goodsid from #g_trade_goodslist group by GoodsId
)
select * from #g_trade_tradelist a join #g_trade_goodslist b on a.TradeID=b.TradeID and b.goodsId in(select goodsId from cte)
and a.tradestatus=5 and a.printexpress=''drop table #g_trade_tradelist
drop table #g_trade_goodslist------------------------------------------
(12 行受影响)(14 行受影响)
TradeID tradestatus printexpress ID GoodsID TradeID
----------- ----------- ---------------------------------------------------------------------------------------------------- ----------- ----------- -----------
1 5 1 1 1
2 5 2 1 2
3 5 3 1 3
4 5 4 1 4(4 行受影响)
if object_id('#g_trade_goodslist') is not null drop table #g_trade_goodslist
create table #g_trade_tradelist(TradeID int identity(1,1) not null,tradestatus int null,printexpress nvarchar(100) null)create table #g_trade_goodslist(ID int identity(1,1) not null,GoodsID int null,TradeID int null)
insert into #g_trade_tradelist
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,''
insert into #g_trade_goodslist
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 1,5 union all
select 2,6 union all
select 2,7 union all
select 2,8 union all
select 2,9 union all
select 3,10 union all
select 3,11 union all
select 3,12
;with cte as(
select top 1 max(goodsId) as goodsid from #g_trade_goodslist group by GoodsId
)
select * from #g_trade_tradelist a join #g_trade_goodslist b on a.TradeID=b.TradeID and b.goodsId in(select goodsId from cte)
and a.tradestatus=5 and a.printexpress=''drop table #g_trade_tradelist
drop table #g_trade_goodslist----------------------------(12 行受影响)(12 行受影响)
TradeID tradestatus printexpress ID GoodsID TradeID
----------- ----------- ---------------------------------------------------------------------------------------------------- ----------- ----------- -----------
1 5 1 1 1
2 5 2 1 2
3 5 3 1 3
4 5 4 1 4
5 5 5 1 5(5 行受影响)
if object_id('#g_trade_goodslist') is not null drop table #g_trade_goodslist
create table #g_trade_tradelist(TradeID int identity(1,1) not null,tradestatus int null,printexpress nvarchar(100) null)create table #g_trade_goodslist(ID int identity(1,1) not null,GoodsID int null,TradeID int null)
insert into #g_trade_tradelist
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,''
insert into #g_trade_goodslist
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 1,5 union all
select 2,6 union all
select 2,7 union all
select 2,8 union all
select 2,9 union all
select 3,10 union all
select 3,11 union all
select 3,12
;with cte as(
select top 1 max(goodsId) as aid from #g_trade_goodslist group by GoodsId order by aid
)
select * from #g_trade_tradelist a join #g_trade_goodslist b on a.TradeID=b.TradeID and b.goodsId in(select aid from cte)
and a.tradestatus=5 and a.printexpress=''drop table #g_trade_tradelist
drop table #g_trade_goodslist
-------------------------(12 行受影响)(12 行受影响)
TradeID tradestatus printexpress ID GoodsID TradeID
----------- ----------- ---------------------------------------------------------------------------------------------------- ----------- ----------- -----------
1 5 1 1 1
2 5 2 1 2
3 5 3 1 3
4 5 4 1 4
5 5 5 1 5(5 行受影响)
应该是;with cte as(
select top 1 max(goodsId) as aid from #g_trade_goodslist
group by GoodsId order by aid )
select * from #g_trade_tradelist a join #g_trade_goodslist b
on a.TradeID=b.TradeID and b.goodsId in(select aid from cte)
and a.tradestatus=5 and a.printexpress=''这个部分了不过这部分内容要写在select * from g_trade_tradelist where()后面,该怎么写呢?(客户端的关系,一定要这样...)我修改后提示当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。
下边这个是对的。
if object_id('Tempdb..#g_trade_tradelist') is not null drop table #g_trade_tradelist
if object_id('Tempdb..#g_trade_goodslist') is not null drop table #g_trade_goodslist
create table #g_trade_tradelist(TradeID int identity(1,1) not null,tradestatus int null,printexpress nvarchar(100) null)
create table #g_trade_goodslist(ID int identity(1,1) not null,GoodsID int null,TradeID int null) insert into #g_trade_tradelist
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,''
insert into #g_trade_goodslist
select 1,1 union all
select 2,2 union all
select 2,3 union all
select 3,4 union all
select 3,5 union all
select 4,6 union all
select 4,7 union all
select 4,8 union all
select 4,9 union all
select 4,10 union all
select 5,11 union all
select 5,12 select * from #g_trade_tradelist a
join #g_trade_goodslist b on a.TradeID=b.TradeID
and a.tradestatus=5 and a.printexpress=''
and b.goodsId in(select top 1 max(goodsid) as goodsid from #g_trade_goodslist group by GoodsId order by count(goodsid) desc
)----------------------------------------------------(12 行受影响)(12 行受影响)
TradeID tradestatus printexpress ID GoodsID TradeID
----------- ----------- ---------------------------------------------------------------------------------------------------- ----------- ----------- -----------
6 5 6 4 6
7 5 7 4 7
8 5 8 4 8
9 5 9 4 9
10 5 10 4 10(5 行受影响)
外面那层必须是select * from g_trade_tradelist where()
最外层没办法用join....
select * from #g_trade_tradelist where TradeID in(
select a.TradeID from #g_trade_tradelist a
join #g_trade_goodslist b
on a.TradeID=b.TradeID and a.tradestatus=5
and a.printexpress=''
and b.goodsId in(select top 1 max(goodsid) as goodsid from #g_trade_goodslist group by GoodsId order by count(goodsid) desc)
)
满足a.tradestatus=5 and a.printexpress='' 的有118个单,查询出来只有1个结果.错了.
select top 1 max(goodsid) as goodsid from #g_trade_goodslist group by GoodsId order by count(goodsid) desc
--查看结果,将上边执行的结果赋给下边,执行下边的语句
select a.TradeID from #g_trade_tradelist a
join #g_trade_goodslist b
on a.TradeID=b.TradeID and a.tradestatus=5
and a.printexpress=''
and b.goodsId = --这里填上边执行的结果
select top 1 max(goodsid) as goodsid from #g_trade_goodslist group by GoodsId order by count(goodsid) desc
这一句错了,这个是从货品表里找到出现次数最多的货品,但是我们要的是满足a.tradestatus=5 and a.printexpress=''这两个条件下出现次数最多的货品.
所以应该是类似
select * from wdgj30.dbo.g_trade_tradelist where TradeID in(
select a.TradeID from wdgj30.dbo.g_trade_tradelist a
join wdgj30.dbo.G_Trade_GoodsList b
on a.TradeID=b.TradeID and a.tradestatus=5
and a.printexpress='' and b.goodsId in(
select top 1 goodsid,count(GoodsID)as c from wdgj30.dbo.G_Trade_GoodsList where TradeID in(
select tradeid from wdgj30.dbo.g_trade_tradelist where tradestatus=5 and printexpress=''
)group by GoodsID order by c desc
)
这样子的.不过我改起来语句感觉很重复,而且也不能运行...您给看看可以怎么改正.麻烦了
use hmail
if object_id('Tempdb..#g_trade_tradelist') is not null drop table #g_trade_tradelist
if object_id('Tempdb..#g_trade_goodslist') is not null drop table #g_trade_goodslist
create table #g_trade_tradelist(TradeID int identity(1,1) not null,tradestatus int null,printexpress nvarchar(100) null)
create table #g_trade_goodslist(ID int identity(1,1) not null,GoodsID int null,TradeID int null)
insert into #g_trade_tradelist
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'' union all
select 5,'a' union all
select 5,''
insert into #g_trade_goodslist
select 1,1 union all
select 2,2 union all
select 2,3 union all
select 3,4 union all
select 3,5 union all
select 4,6 ---按照你的要求,这个是最终查询结果
select * from #g_trade_tradelist where TradeID in
(
---取符合条件的TradeID
select c.TradeID from #g_trade_tradelist c
join #g_trade_goodslist d on c.TradeID=d.TradeID and d.goodsid in
(
--分组,降序排列,取第一个goodsId
select top 1 goodsid from
(
---查询#g_trade_tradelist中符合tradestatus=5 and printexpress='' 的记录
select a.TradeID,b.goodsid
from #g_trade_tradelist a
join #g_trade_goodslist b
on a.TradeID=b.TradeID and a.tradestatus=5 and a.printexpress=''
) t group by goodsid order by count(goodsid) desc
)
)
----------------------(6 行受影响)(6 行受影响)
TradeID tradestatus printexpress
----------- ----------- -------------
2 5
3 5 (2 行受影响)
select * from #g_trade_tradelist where TradeID in
(
---取符合条件的TradeID
select c.TradeID from #g_trade_tradelist c
join #g_trade_goodslist d on c.TradeID=d.TradeID and d.goodsid in
(
--分组,降序排列,取第一个goodsId
select top 1 goodsid from
(
---查询#g_trade_tradelist中符合tradestatus=5 and printexpress='' 的记录
select a.TradeID,b.goodsid
from #g_trade_tradelist a
join #g_trade_goodslist b
on a.TradeID=b.TradeID and a.tradestatus=5 and a.printexpress=''
) t group by goodsid order by count(goodsid) desc
)
)
select c.TradeID from #g_trade_tradelist c
join #g_trade_goodslist d on c.TradeID=d.TradeID and d.goodsid in
这一句,最后搜出来的tradeid并没有满足tradestatus=5 and printexpress=''呢.是不是在这一句后面直接补上这两个条件就可以了?还有我上面那样子改为什么会提示出错呢?
select * from #g_trade_tradelist where TradeID in
(
---取符合条件的TradeID
select c.TradeID from #g_trade_tradelist c
join #g_trade_goodslist d on c.TradeID=d.TradeID
and c.tradestatus=5 and c.printexpress=''-----加上限制
and d.goodsid in
(
--分组,降序排列,取第一个goodsId
select top 1 goodsid from
(
---查询#g_trade_tradelist中符合tradestatus=5 and printexpress='' 的记录
select a.TradeID,b.goodsid
from #g_trade_tradelist a
join #g_trade_goodslist b
on a.TradeID=b.TradeID and a.tradestatus=5 and a.printexpress=''----这里实际上已经加过限制了
) t group by goodsid order by count(goodsid) desc
)
)