SELECT (SELECT ID FROM TABLE WHERE ID<50) AS A1,
(SELECT ID FROM TABLE WHERE ID<100 AND ID>=50) AS A2,
(SELECT ID FROM TABLE WHERE ID<200 AND ID>=100) AS A2
...
FROM TABLE
(SELECT ID FROM TABLE WHERE ID<100 AND ID>=50) AS A2,
(SELECT ID FROM TABLE WHERE ID<200 AND ID>=100) AS A2
...
FROM TABLE
Lfield 有值如下:1,2,3,4,5,6,7,8,9
我要查詢出如下結果:
Mfield(以Lfield<4為條件) Nfield(以3<Lfield<7為條件) Xfield(以Lfield>6為條件)
1 4 7
2 5 8
3 6 9
大體就是這樣了,不過隻是舉一個例子說明。
(SELECT ID FROM TABLE WHERE Lfield<7 AND Lfield>=4) AS Nfield,
(SELECT ID FROM TABLE WHERE Lfield>=7) AS Xfield
FROM TABLE
select (select a.cqty from prdidt where cdate>'2003/01/01' and cdate<'2003/02/01' ) as col1, (select a.cqty from prdidt where cdate>'2003/02/01' and cdate<'2003/03/01') as col2 from prdidt as a
不行啊。
錯誤如下:
子查詢的傳回值不只一個。這種狀況在子查詢之後有 =、!=、<、<=、>、>= 或是子查 詢作為運算式使用時是不允許的。
库表
EmpId NameId Mark
-------------------------
175 3 30
175 4 60
175 8 20
178 3 25
178 4 35
178 8 90通过sql想返回
EmpId 3 4 8
--------------------------
175 30 60 20
178 25 35 90declare @sql varchar(8000)
set @sql = 'select EmpId,'select @sql = @sql + 'sum(case NameId when '''+NameId+'''
then Mark else 0 end) as ['+NameId+'],'
from (select distinct NameId from 库表) aselect @sql = left(@sql,len(@sql)-1) + ' from 库表 group by EmpId'exec(@sql)
go
SELECT identity(int,1,1) as id , Lfield into #1 FROM TABLE WHERE Lfield<7 AND Lfield>=4
SELECT identity(int,1,1) as id , Lfield into #2 FROM TABLE WHERE Lfield>=7
select a.Lfield as mLfield ,b.Lfield as nLfield ,c.Lfield as xLfield
from # a full join #1 b on a.id=b.id full join #2 c on a.id=c.id
但我不是想求和啊?實事我就是想要多筆記錄。
剛試了你的方法,產生了多筆sum().
我很奇怪,為什麼也有多筆,sum()就可以?
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
出现这个啊。
--------
a 34
a 56
b 34
b 45===>>>a b
--------
a ??<<====这里你要什么是34,还是56??你都没说sql怎么知道?你怎么放???