order by嵌套子查询问题

2017-04-07 09:24:15 880 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL窗口函数实践笔记（五）——用LEAD（）窗口函数执行和LAG（）相反的查询用LEAD()取下一下amount与本行的amount的差，如果差大于300，则新增字段amount_diff 写入差值，如果小于300,则写入文本“减弱” 阅读详情

两张表，一张supply表，一张tree表，我要查询所有的supply中的信息，但是要根据tree表中的treename排序；

单独查没有问题，嵌套在一起就出现子查询超过一行的问题，请问怎么解决？？

...全文

告别OLE！Delphi操作Excel的3种轻量级方案横向评测（FlexCel/XLSReadWriteII/LibXL）本文深入评测了Delphi操作Excel的三种轻量级方案：FlexCel、XLSReadWriteII和LibXL。通过技术架构、API设计、性能测试和实战案例的全面对比，帮助开发者选择最适合的工具，提升Excel文件处理效率，告别传统OLE方案的依赖和性能问题。阅读详情

yinaoxiao7661

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

二月十六版主 2017-04-08

打赏
举报

引用 19 楼 yinaoxiao7661 的回复:

看着对劲啊。。。

gw6328 2017-04-07

打赏
举报

order by treename 就是最正常的一种方式。但是看楼主不是想要这个，你是想要treename的数量来排序？那你试一下这个 ;with cte as ( select * from xxx ,xxx from xxxxxx --你上面个查询 ) ,c1 as ( select count(*) as ct ,treename from xxxxx --你下面个查询 ) select * from cte a join c1 b on a.treename=b.treename order by b.ct desc;

yinaoxiao7661 2017-04-07

打赏
举报

引用 18 楼 sinat_28984567 的回复:

[quote=引用 17 楼 yinaoxiao7661 的回复:]
[quote=引用 16 楼 sinat_28984567 的回复:]
[quote=引用 15 楼 yinaoxiao7661 的回复:]
你这个是用什么写的？看不懂啊，我只会用MySQL

就是为了测试方便用mssql写的，语句是一样的，改一下表名字就行了。试试这个

SELECT  *

FROM    supply a

        JOIN tree b ON a.treeId = b.treeId

        JOIN ( SELECT   supply.treeId ,

                        COUNT(1) AS num

               FROM     supply

                        JOIN  tree ON supply.treeId = tree.treeId

               GROUP BY supply.treeId

             ) t ON b.treeId = t.treeId

ORDER BY t.num DESC

[/quote]
不行，排序是乱的

[/quote]

把得到的num列出来看一下[/quote]

yinaoxiao7661 2017-04-07

打赏
举报

引用 5 楼 sinat_28984567 的回复:

写漏了一个group by 试试这个

SELECT  a.* ,

        b.*

FROM    supply a

        JOIN tree b ON a.treeid = b.treeid

        JOIN ( SELECT   treename ,

                        COUNT(treename) AS num

               FROM     supply

                        JOIN tree ON supply.treeid = tree.treeid

               GROUP BY treename

             ) t ON b.treename = t.treename

ORDER BY t.num

还是不行，加上group by 排序是乱的

二月十六版主 2017-04-07

打赏
举报

写漏了一个group by 试试这个

SELECT  a.* ,
        b.*
FROM    supply a
        JOIN tree b ON a.treeid = b.treeid
        JOIN ( SELECT   treename ,
                        COUNT(treename) AS num
               FROM     supply
                        JOIN tree ON supply.treeid = tree.treeid
               GROUP BY treename
             ) t ON b.treename = t.treename
ORDER BY t.num

yinaoxiao7661 2017-04-07

打赏
举报

引用 3 楼 sinat_28984567 的回复:

试试这个

SELECT  a.* ,

        b.*

FROM    supply a

        JOIN tree b ON a.treeid = b.treeid

        JOIN ( SELECT   treename ,

                        COUNT(treename) AS num

               FROM     supply

                        JOIN tree ON supply.treeid = tree.treeid

             ) t ON b.treename = t.treename

ORDER BY t.num

不行啊，结果只有一棵树的数据，其他的怎么没有

二月十六版主 2017-04-07

打赏
举报

试试这个

SELECT  a.* ,
        b.*
FROM    supply a
        JOIN tree b ON a.treeid = b.treeid
        JOIN ( SELECT   treename ,
                        COUNT(treename) AS num
               FROM     supply
                        JOIN tree ON supply.treeid = tree.treeid
             ) t ON b.treename = t.treename
ORDER BY t.num

yinaoxiao7661 2017-04-07

打赏
举报

引用楼主 yinaoxiao7661 的回复:

两张表，一张supply表，一张tree表，我要查询所有的supply中的信息，但是要根据tree表中的treename排序；单独查没有问题，嵌套在一起就出现子查询超过一行的问题，请问怎么解决？？

supply表和tree表连接查询，查询结果根据查询出来的treename分组统计数量，按数量倒序排序；意思就是查询结果按树木的供应数量倒序排序

卖水果的net 版主 2017-04-07

打赏
举报

你这是要按什么排序啊？说一下需求吧；

二月十六版主 2017-04-07

打赏
举报

引用 17 楼 yinaoxiao7661 的回复:

[quote=引用 16 楼 sinat_28984567 的回复:] [quote=引用 15 楼 yinaoxiao7661 的回复:] 你这个是用什么写的？看不懂啊，我只会用MySQL

就是为了测试方便用mssql写的，语句是一样的，改一下表名字就行了。试试这个

SELECT  *
FROM    supply a
        JOIN tree b ON a.treeId = b.treeId
        JOIN ( SELECT   supply.treeId ,
                        COUNT(1) AS num
               FROM     supply
                        JOIN  tree ON supply.treeId = tree.treeId
               GROUP BY supply.treeId
             ) t ON b.treeId = t.treeId
ORDER BY t.num DESC

[/quote] 不行，排序是乱的

[/quote] 把得到的num列出来看一下

yinaoxiao7661 2017-04-07

打赏
举报

引用 16 楼 sinat_28984567 的回复:

[quote=引用 15 楼 yinaoxiao7661 的回复:]
你这个是用什么写的？看不懂啊，我只会用MySQL

就是为了测试方便用mssql写的，语句是一样的，改一下表名字就行了。试试这个

SELECT  *

FROM    supply a

        JOIN tree b ON a.treeId = b.treeId

        JOIN ( SELECT   supply.treeId ,

                        COUNT(1) AS num

               FROM     supply

                        JOIN  tree ON supply.treeId = tree.treeId

               GROUP BY supply.treeId

             ) t ON b.treeId = t.treeId

ORDER BY t.num DESC

[/quote]
不行，排序是乱的

二月十六版主 2017-04-07

打赏
举报

引用 15 楼 yinaoxiao7661 的回复:

你这个是用什么写的？看不懂啊，我只会用MySQL

就是为了测试方便用mssql写的，语句是一样的，改一下表名字就行了。试试这个

SELECT  *
FROM    supply a
        JOIN tree b ON a.treeId = b.treeId
        JOIN ( SELECT   supply.treeId ,
                        COUNT(1) AS num
               FROM     supply
                        JOIN  tree ON supply.treeId = tree.treeId
               GROUP BY supply.treeId
             ) t ON b.treeId = t.treeId
ORDER BY t.num DESC

yinaoxiao7661 2017-04-07

打赏
举报

引用 14 楼 sinat_28984567 的回复:

用mssql写的测试数据好弄。语句应该差不多。楼主参考一下。

--测试数据
if not object_id(N'Tempdb..#supply') is null
	drop table #supply
Go
Create table #supply([supplyid] int,[treeTypeID] int,[treeId] int,[userId] int,[releaseTIme] Date,[validityPeriod] Date,[supplyDescribe] nvarchar(27),[supplyPlace] nvarchar(22),[supplySum] int)
Insert #supply
select 1,1,1,1,'2017-3-14','2017-8-14',N'桂花、香樟',N'江苏',2000 union all
select 10,2,1,5,'2017-3-30','2017-8-22',N'光叶白兰、连翘',N'山东',2200 union all
select 11,3,2,1,'2017-3-31','2017-8-23',N'青桐、光叶白兰',N'天津',2300 union all
select 12,3,3,2,'2017-3-22','2017-8-24',N'枫杨、青桐',N'江西',2400 union all
select 13,3,3,3,'2017-3-24','2017-8-25',N'意杨、枫杨',N'福建',2500 union all
select 14,3,3,4,'2017-3-25','2017-8-26',N'丰花月季、意杨',N'四川',2600 union all
select 15,3,3,5,'2017-3-23','2017-8-27',N'红枫、丰花月季',N'重庆',2700 union all
select 16,4,4,1,'2017-3-19','2017-8-28',N'侧柏、红枫',N'北京',2800 union all
select 17,4,4,2,'2017-3-13','2017-8-29',N'竹柏、侧柏',N'江苏',2900 union all
select 18,4,4,3,'2017-3-26','2017-8-30',N'柳杉、竹柏',N'浙江',3000 union all
select 19,4,4,4,'2017-3-10','2017-8-13',N'国槐、柳杉',N'安徽',3100 union all
select 2,1,1,2,'2017-3-15','2017-8-29',N'梅花、香樟',N'浙江',1000 union all
select 20,4,4,5,'2017-3-11','2017-8-12',N'白蜡树、国槐',N'湖南',3200 union all
select 21,5,5,1,'2017-3-12','2017-8-11',N'落羽松、雪松',N'湖北',3300 union all
select 22,5,5,2,'2017-3-13','2017-8-10',N'雪松、国槐',N'上海',3400 union all
select 23,5,5,3,'2017-3-9','2017-8-9',N'罗汉松、白蜡树',N'河北',3500 union all
select 24,5,5,4,'2017-3-8','2017-8-8',N'木荷、罗汉松',N'河南',3500 union all
select 25,5,5,5,'2017-3-7','2017-8-7',N'樱花、木荷',N'海南',3000 union all
select 26,2,2,6,'2017-4-8','2017-9-8',N'连翘、法桐',N'江苏',10000 union all
select 3,1,1,3,'2017-3-21','2017-8-15',N'桂花、棕榈',N'安徽',1500 union all
select 4,1,1,4,'2017-3-20','2017-8-16',N'红花槐、棕榈',N'湖南',1600 union all
select 5,1,1,5,'2017-3-18','2017-8-17',N'桢楠、红花槐',N'湖北',1700 union all
select 6,2,2,1,'2017-3-17','2017-8-18',N'池杉、桢楠',N'上海',1800 union all
select 7,2,2,2,'2017-3-28','2017-8-19',N'木槿、海滨木槿',N'河北',1900 union all
select 8,2,2,3,'2017-3-27','2017-8-20',N'法桐、桢楠',N'河南',2000 union all
select 9,2,2,4,'2017-3-29','2017-8-21',N'连翘、法桐',N'山西',2100
GO
if not object_id(N'Tempdb..#tree') is null
	drop table #tree
Go
Create table #tree([treeId] int,[treeTypeId] int,[treeName] nvarchar(23))
Insert #tree
select 1,1,N'落羽松' union all
select 2,1,N'金钱松' union all
select 3,1,N'水杉' union all
select 4,1,N'池杉' union all
select 5,1,N'法桐'
Go
--测试数据结束
SELECT  *
FROM    #supply a
        JOIN #tree b ON a.treeId = b.treeId
        JOIN ( SELECT   #supply.treeId ,
                        COUNT(1) AS num
               FROM     #supply
                        JOIN #tree ON #supply.treeId = #tree.treeId
               GROUP BY #supply.treeId
             ) t ON b.treeId = t.treeId
ORDER BY t.num DESC

你这个是用什么写的？看不懂啊，我只会用MySQL

二月十六版主 2017-04-07

打赏
举报

用mssql写的测试数据好弄。语句应该差不多。楼主参考一下。

--测试数据

if not object_id(N'Tempdb..#supply') is null

	drop table #supply

Go

Create table #supply([supplyid] int,[treeTypeID] int,[treeId] int,[userId] int,[releaseTIme] Date,[validityPeriod] Date,[supplyDescribe] nvarchar(27),[supplyPlace] nvarchar(22),[supplySum] int)

Insert #supply

select 1,1,1,1,'2017-3-14','2017-8-14',N'桂花、香樟',N'江苏',2000 union all

select 10,2,1,5,'2017-3-30','2017-8-22',N'光叶白兰、连翘',N'山东',2200 union all

select 11,3,2,1,'2017-3-31','2017-8-23',N'青桐、光叶白兰',N'天津',2300 union all

select 12,3,3,2,'2017-3-22','2017-8-24',N'枫杨、青桐',N'江西',2400 union all

select 13,3,3,3,'2017-3-24','2017-8-25',N'意杨、枫杨',N'福建',2500 union all

select 14,3,3,4,'2017-3-25','2017-8-26',N'丰花月季、意杨',N'四川',2600 union all

select 15,3,3,5,'2017-3-23','2017-8-27',N'红枫、丰花月季',N'重庆',2700 union all

select 16,4,4,1,'2017-3-19','2017-8-28',N'侧柏、红枫',N'北京',2800 union all

select 17,4,4,2,'2017-3-13','2017-8-29',N'竹柏、侧柏',N'江苏',2900 union all

select 18,4,4,3,'2017-3-26','2017-8-30',N'柳杉、竹柏',N'浙江',3000 union all

select 19,4,4,4,'2017-3-10','2017-8-13',N'国槐、柳杉',N'安徽',3100 union all

select 2,1,1,2,'2017-3-15','2017-8-29',N'梅花、香樟',N'浙江',1000 union all

select 20,4,4,5,'2017-3-11','2017-8-12',N'白蜡树、国槐',N'湖南',3200 union all

select 21,5,5,1,'2017-3-12','2017-8-11',N'落羽松、雪松',N'湖北',3300 union all

select 22,5,5,2,'2017-3-13','2017-8-10',N'雪松、国槐',N'上海',3400 union all

select 23,5,5,3,'2017-3-9','2017-8-9',N'罗汉松、白蜡树',N'河北',3500 union all

select 24,5,5,4,'2017-3-8','2017-8-8',N'木荷、罗汉松',N'河南',3500 union all

select 25,5,5,5,'2017-3-7','2017-8-7',N'樱花、木荷',N'海南',3000 union all

select 26,2,2,6,'2017-4-8','2017-9-8',N'连翘、法桐',N'江苏',10000 union all

select 3,1,1,3,'2017-3-21','2017-8-15',N'桂花、棕榈',N'安徽',1500 union all

select 4,1,1,4,'2017-3-20','2017-8-16',N'红花槐、棕榈',N'湖南',1600 union all

select 5,1,1,5,'2017-3-18','2017-8-17',N'桢楠、红花槐',N'湖北',1700 union all

select 6,2,2,1,'2017-3-17','2017-8-18',N'池杉、桢楠',N'上海',1800 union all

select 7,2,2,2,'2017-3-28','2017-8-19',N'木槿、海滨木槿',N'河北',1900 union all

select 8,2,2,3,'2017-3-27','2017-8-20',N'法桐、桢楠',N'河南',2000 union all

select 9,2,2,4,'2017-3-29','2017-8-21',N'连翘、法桐',N'山西',2100

GO

if not object_id(N'Tempdb..#tree') is null

	drop table #tree

Go

Create table #tree([treeId] int,[treeTypeId] int,[treeName] nvarchar(23))

Insert #tree

select 1,1,N'落羽松' union all

select 2,1,N'金钱松' union all

select 3,1,N'水杉' union all

select 4,1,N'池杉' union all

select 5,1,N'法桐'

Go

--测试数据结束

SELECT  *

FROM    #supply a

        JOIN #tree b ON a.treeId = b.treeId

        JOIN ( SELECT   #supply.treeId ,

                        COUNT(1) AS num

               FROM     #supply

                        JOIN #tree ON #supply.treeId = #tree.treeId

               GROUP BY #supply.treeId

             ) t ON b.treeId = t.treeId

ORDER BY t.num DESC

gw6328 2017-04-07

打赏
举报

引用 11 楼 yinaoxiao7661 的回复:

[quote=引用 7 楼 jinfengyiye 的回复:] order by treename 就是最正常的一种方式。但是看楼主不是想要这个，你是想要treename的数量来排序？那你试一下这个 ;with cte as ( select * from xxx ,xxx from xxxxxx --你上面个查询 ) ,c1 as ( select count(*) as ct ,treename from xxxxx --你下面个查询 ) select * from cte a join c1 b on a.treename=b.treename order by b.ct desc;

对，我是想通过统计查询结果中treename的数量来排序，我看了下你这种查询方式我不太懂，复制粘贴后查询报错

[/quote] 我这个是mssql 没注意看到你说的是mysql ,mysql应该可以用扩展表吧。你把 cte 和 c1 直接用两个（）包起来 select * from (第一个查询) a join (第二个查询) b on a.treename=b.treename order by b.ct desc;

yinaoxiao7661 2017-04-07

打赏
举报

引用 8 楼 sinat_28984567 的回复:

贴点测试数据，我自己建表试试

supply表：

1 1 1 1 2017-3-14 2017-8-14 桂花、香樟江苏 2000
10 2 1 5 2017-3-30 2017-8-22 光叶白兰、连翘山东 2200
11 3 2 1 2017-3-31 2017-8-23 青桐、光叶白兰天津 2300
12 3 3 2 2017-3-22 2017-8-24 枫杨、青桐江西 2400
13 3 3 3 2017-3-24 2017-8-25 意杨、枫杨福建 2500
14 3 3 4 2017-3-25 2017-8-26 丰花月季、意杨四川 2600
15 3 3 5 2017-3-23 2017-8-27 红枫、丰花月季重庆 2700
16 4 4 1 2017-3-19 2017-8-28 侧柏、红枫北京 2800
17 4 4 2 2017-3-13 2017-8-29 竹柏、侧柏江苏 2900
18 4 4 3 2017-3-26 2017-8-30 柳杉、竹柏浙江 3000
19 4 4 4 2017-3-10 2017-8-13 国槐、柳杉安徽 3100
2 1 1 2 2017-3-15 2017-8-29 梅花、香樟浙江 1000
20 4 4 5 2017-3-11 2017-8-12 白蜡树、国槐湖南 3200
21 5 5 1 2017-3-12 2017-8-11 落羽松、雪松湖北 3300
22 5 5 2 2017-3-13 2017-8-10 雪松、国槐上海 3400
23 5 5 3 2017-3-9 2017-8-9 罗汉松、白蜡树河北 3500
24 5 5 4 2017-3-8 2017-8-8 木荷、罗汉松河南 3500
25 5 5 5 2017-3-7 2017-8-7 樱花、木荷海南 3000
26 2 2 6 2017-4-8 2017-9-8 连翘、法桐江苏 10000
3 1 1 3 2017-3-21 2017-8-15 桂花、棕榈安徽 1500
4 1 1 4 2017-3-20 2017-8-16 红花槐、棕榈湖南 1600
5 1 1 5 2017-3-18 2017-8-17 桢楠、红花槐湖北 1700
6 2 2 1 2017-3-17 2017-8-18 池杉、桢楠上海 1800
7 2 2 2 2017-3-28 2017-8-19 木槿、海滨木槿河北 1900
8 2 2 3 2017-3-27 2017-8-20 法桐、桢楠河南 2000
9 2 2 4 2017-3-29 2017-8-21 连翘、法桐山西 2100
tree表：

1 1 落羽松
2 1 金钱松
3 1 水杉
4 1 池杉
5 1 法桐

yinaoxiao7661 2017-04-07

打赏
举报

引用 7 楼 jinfengyiye 的回复:

order by treename 就是最正常的一种方式。

但是看楼主不是想要这个，你是想要treename的数量来排序？
那你试一下这个
;with cte as (
select * from xxx ,xxx from xxxxxx --你上面个查询
) ,c1 as (
select count(*) as ct ,treename from xxxxx --你下面个查询
)
select * from cte a join c1 b on a.treename=b.treename order by b.ct desc;

对，我是想通过统计查询结果中treename的数量来排序，我看了下你这种查询方式我不太懂，复制粘贴后查询报错

yinaoxiao7661 2017-04-07

打赏
举报

引用 9 楼 baidu_35289351 的回复:

在楼主的基础上；另外，楼主的2个表有相同的字段名称，为了查询结果不出现2个相同字段，建议 tree表的treeId用别名 T_treeId select * from ( select s.supplyId,s.treeType,s.treeId,s.userId,s.releaseTime,s.validPeriod,s.supplyDescribe,s.supplyPlace,s.supplySum, t.treeId as T_treeId,t.treeTypeId as T_treeTypeId,t.treeName from supply s,tree t where s.treeId=t.treeId)a order by T_treeId

查询结果不是通过treeId来排序的，是通过查询结果的treename的数量来排序

baidu_35289351 2017-04-07

打赏
举报

在楼主的基础上；另外，楼主的2个表有相同的字段名称，为了查询结果不出现2个相同字段，建议 tree表的treeId用别名 T_treeId select * from ( select s.supplyId,s.treeType,s.treeId,s.userId,s.releaseTime,s.validPeriod,s.supplyDescribe,s.supplyPlace,s.supplySum, t.treeId as T_treeId,t.treeTypeId as T_treeTypeId,t.treeName from supply s,tree t where s.treeId=t.treeId)a order by T_treeId

二月十六版主 2017-04-07

打赏
举报

贴点测试数据，我自己建表试试

order by嵌套子查询问题

相关推荐