討教一個查詢語句的寫法

[kokokokokoko]

如何把一個有n筆記錄的字段按條件分割產生多個字段？

[kokokokokoko]

最好是一條語句。

[Hahahahahaha]

例子？？

[caiyunxia]

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

[kokokokokoko]

比如：
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
大體就是這樣了，不過隻是舉一個例子說明。

[caiyunxia]

SELECT (SELECT ID FROM TABLE WHERE Lfield<4) AS Mfield,
(SELECT ID FROM TABLE WHERE Lfield<7 AND Lfield>=4) AS Nfield,
(SELECT ID FROM TABLE WHERE Lfield>=7) AS Xfield
FROM TABLE

[kokokokokoko]

to caiyunxia(monkey) :
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
不行啊。
錯誤如下：
子查詢的傳回值不只一個。這種狀況在子查詢之後有 =、!=、<、<=、>、>= 或是子查 詢作為運算式使用時是不允許的。

[happydreamer]

交叉表？

Example:
库表
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 90

declare @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 库表) a

select @sql = left(@sql,len(@sql)-1) + ' from 库表 group by EmpId'

exec(@sql)
go

[caiyunxia]

SELECT identity(int,1,1) as id , Lfield into # FROM TABLE WHERE Lfield<4
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

[pengdali]

select

(select sum(a.cqty) from prdidt where cdate>'2003/01/01' and cdate<'2003/02/01' ) as col1,

(select sum(a.cqty) from prdidt where cdate>'2003/02/01' and cdate<'2003/03/01') as col2

from prdidt as a

[kokokokokoko]

我倒沒有一定要如此排序的意思
但我隻想要一句耶。

[kokokokokoko]

to pengdali(大力) ：
但我不是想求和啊？實事我就是想要多筆記錄。

[kokokokokoko]

to pengdali(大力) ：
剛試了你的方法，產生了多筆sum().

[kokokokokoko]

to pengdali(大力) ：
我很奇怪，為什麼也有多筆，sum()就可以？

[pengdali]

是利用sum去掉了null值，这样写是对的！

[pengdali]

你用max()和min() avg() 也是一样的!

[kokokokokoko]

不對了，這樣子隻是產生多條相同的sum()。而我是要多條明細啊！

[kokokokokoko]

各位老大，幫忙啊!!!!!!!!!!!!!!!!!

[nhjoy]

服务器: 消息 512，级别 16，状态 1，行 1
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。


出现这个啊。

[pengdali]

你到底想要什么？

[pengdali]

a b
--------
a 34
a 56
b 34
b 45

===>>>

a b
--------
a ??<<====这里你要什么是34,还是56??你都没说sql怎么知道？你怎么放？？？