研究一下 - 编程论坛

#2

西风独自凉2008-11-07 10:05

建表﹕
select * into yy    from (select   1  as id  , 2008  as date ,  5   as qty  union all select 1,   2009 ,  4
         union all select 2 ,  2008 ,  5 union all select 2 ,  2009,   6 union all select 2 ,  2008,   7 union all select 2 ,  2009,  4 )  tt
根據上面所建立的表寫sql得出下面結果  :
id  date    qty
1   2009    4
2   2009    6

#3

happynight2008-11-07 13:59

[bo][un]西风独自凉[/un] 在 2008-11-7 09:29 的发言：[/bo]

建表﹕
select * into xx from (
select '鋁' as ScrapeType,'片材' as RawMType  ,23.00   as qty union all
select '鐵'    ,'片材',799.60    union all
select '鋁'    ,'料渣',16.20    union all
select '鋼'    ,'料渣' ...

很典型的交叉表问题若使用的是2000的话
select * into #ls1 from (
select '鋁' as ScrapeType,'片材' as RawMType  ,23.00   as qty union all
select '鐵'    ,'片材',799.60    union all
select '鋁'    ,'料渣',16.20    union all
select '鋼'    ,'料渣',32.00    union all
select '鐵'    ,'料渣',105.00    union all
select '鋁'    ,'料頭',1.50    union all
select '鐵'    ,'料頭',16.40    union all
select '鐵'    ,'片材',10 )dd


SELECT * FROM #ls1
SELECT RawMType,SUM(CASE ScrapeType WHEN  '銅' THEN qty ELSE 0 END) AS 銅,
SUM(CASE ScrapeType WHEN  '鐵' THEN qty ELSE 0 END) AS 鐵,
SUM(CASE ScrapeType WHEN  '鋁' THEN qty ELSE 0 END) AS 鋁,
SUM(CASE ScrapeType WHEN  '鋼' THEN qty ELSE 0 END) AS 鋼
FROM #ls1 GROUP BY RawMType

若是2000以上的版本好象数据库本身就支持交叉表查询

[[it] 本帖最后由 happynight 于 2008-11-7 14:00 编辑 [/it]]

#4

happynight2008-11-07 14:03

[bo][un]西风独自凉[/un] 在 2008-11-7 10:05 的发言：[/bo]

建表﹕
select * into yy from (select 1 as id , 2008 as date , 5 as qty union all select 1, 2009 , 4
union all select 2 , 2008 , 5 union all select 2 , 2009, 6 union all select ...

看不懂原始数据和最终结果之间有什么逻辑关系详细说明下

#5

西风独自凉2008-11-07 15:51

[bo][un]happynight[/un] 在 2008-11-7 14:03 的发言：[/bo]

看不懂原始数据和最终结果之间有什么逻辑关系详细说明下

得出日期最大和當前最大日期的最大qty 以第一列分組

[[it] 本帖最后由西风独自凉于 2008-11-7 15:55 编辑 [/it]]

#6

西风独自凉2008-11-07 15:53

[bo][un]happynight[/un] 在 2008-11-7 13:59 的发言：[/bo]

很典型的交叉表问题若使用的是2000的话
select * into #ls1 from (
select '鋁' as ScrapeType,'片材' as RawMType  ,23.00   as qty union all
select '鐵'    ,'片材',799.60    union all
select '鋁'     ...

數據一多﹐你還這樣寫﹖
這種方法不實用

#7

happynight2008-11-07 17:25

[bo][un]西风独自凉[/un] 在 2008-11-7 15:53 的发言：[/bo]

數據一多﹐你還這樣寫﹖
這種方法不實用

呵呵有动态版
declare @sql varchar(8000)
set @sql=' '
select @sql=@sql + 'sum(case ScrapeType when'''+ ScrapeType +'''
            then qty else 0 end) as'''+ ScrapeType +''','
from(select distinct ScrapeType from #ls1) as a

select @sql=' SELECT RawMType  As 类别,'
            + @sql+ '  from #ls1  group by RawMType  '
exec(@sql)
-----
时间比较匆忙没调试

[[it] 本帖最后由 happynight 于 2008-11-7 17:29 编辑 [/it]]

#8

happynight2008-11-07 17:27

下班了下个问题回家慢慢研究

#9

西风独自凉2008-11-08 11:58

一樓答案﹕
第一種動態﹕
Declare @sql varChar(8000)
Set @sql = 'Select RawMType'
Select @sql = @sql + ',Sum(Case ScrapeType When ''' + rTrim(ScrapeType) + ''' Then Qty  End)' +rTrim(ScrapeType)+''
From (Select Distinct ScrapeType From xx) As A
Select @sql = @sql+'  From xx  Group By RawMType'
Print @sql
Exec (@sql)

第二種（寫死了的）﹕
Select a.RawMType,Sum(Case a.ScrapeType When '銅' Then isnull(a.qty,0)  End)銅,
Sum(Case a.ScrapeType When '鐵' Then isnull(a.qty,0)  End)鐵,
Sum(Case a.ScrapeType When '鋁' Then isnull(a.qty,0)  End)鋁 ,
Sum(Case a.ScrapeType When '鋼' Then isnull(a.qty,0)  End)鋼,
  Sum(Case a.ScrapeType When '其它' Then isnull(a.qty,0)  End)其它
From xx a    Group By RawMType

#10

西风独自凉2008-11-08 12:02

二樓答案﹕
Select xx.ID,xx.Date, max(xx.Qty) As Qty From xx Inner Join (
Select ID,Max(Date) As Date From XX Group By ID ) As A
on xx.ID=A.ID And xx.Date = A.Date Group By xx.ID,xx.Date

#11

coolfaith2008-11-11 23:06

小弟遇到类似的问题，也用了动态的方法。但是动态的字段有的涉及到字符串，提示
sum or average aggregate 运算不能以 varchar 数据类型作为参数。
请指教
小弟的帖子举了些数据例子，可否帮忙看看，不胜感激，用的是SQL2000
https://bbs.bccn.net/viewthread.php?tid=243647&pid=1435864&page=1&extra=page%3D1#pid1435864

#12

happynight2008-11-12 22:18

你可以不用SUM 改用MAX的试下

#13

coolfaith2008-11-13 09:46

可以了，谢谢