select a.num1,b.num2 from
(SELECT COUNT(*) AS NUM1,SCID FROM BIAO1 GROUP BY SCID WHERE STATE=2 ORDER BY NUM1 DESC) a,
(
SELECT COUNT(*) AS NUM2,SCIDFROM BIAO1 GROUP BY SCID WHERE STATE=1 ORDER BY NUM1 DESC ) b
where a.scid=b.scid
(SELECT COUNT(*) AS NUM1,SCID FROM BIAO1 GROUP BY SCID WHERE STATE=2 ORDER BY NUM1 DESC) a,
(
SELECT COUNT(*) AS NUM2,SCIDFROM BIAO1 GROUP BY SCID WHERE STATE=1 ORDER BY NUM1 DESC ) b
where a.scid=b.scid
union all
SELECT COUNT(*) AS NUM2 FROM BIAO1 WHERE STATE=1 GROUP BY SCID ORDER BY NUM1 DESC这样就合并成一个结果集了,接着你要怎么处理都可以拉
yesterday2000(一笑而过) 的方法
服务器: 消息 1033,级别 15,状态 1,行 2
除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。
服务器: 消息 156,级别 15,状态 1,行 4
在关键字 'ORDER' 附近有语法错误。 LoveSQL(GG) ( )
会存在
服务器: 消息 205,级别 16,状态 1,行 2
包含 UNION 运算符的 SQL 语句中的所有查询都必须在目标列表中具有相同数目的表达式。
都会出错的.我第一条SQL查询出:
SC_ID NUM1
AAA 42
BBB 21
CCC 11第二条:
SC_ID NUM2
CCC 45
AAA 10
BBB 9我想得到的结果是
SC_ID NUM1 NUM2
CCC 11 45
AAA 42 10
BBB 21 9
你们明白我要说什么吗
SELECT COUNT(*) AS NUM1,SCID into #a FROM BIAO1 GROUP BY SCID WHERE STATE=2 ORDER BY NUM1 DESC
SELECT COUNT(*) AS NUM2,SCID into #b FROM BIAO1 GROUP BY SCID WHERE STATE=1 ORDER BY NUM1 DESC
select a.scid as sc_if,a.num1,b.num2
from #a a,#b b where a.scid=b.scid
from BIAO1
order by (num1+num2) desc
union all
SELECT COUNT(*) AS NUM2 FROM BIAO1 WHERE STATE=1 GROUP BY SCID ORDER BY NUM1 DESC