表:sales_Contract
字段内容如下:
goods_ID sales_Contract_Code_Name sales_Number Sales_Money
1 100100XS1 100 200
1 100101XS1 200 400
2 100101XS2 20 100
2 100100XS2 30 150
要求在StringGrid中显示成如下效果:
100100 100101
goods_id sales_Number sales_money sales_number sales_money
1 100 200 200 400
2 30 150 20 100能否用SQL直接得出这样的结果?
如不能有没速度最快的方法?数据比效多。
字段内容如下:
goods_ID sales_Contract_Code_Name sales_Number Sales_Money
1 100100XS1 100 200
1 100101XS1 200 400
2 100101XS2 20 100
2 100100XS2 30 150
要求在StringGrid中显示成如下效果:
100100 100101
goods_id sales_Number sales_money sales_number sales_money
1 100 200 200 400
2 30 150 20 100能否用SQL直接得出这样的结果?
如不能有没速度最快的方法?数据比效多。
(
goods_ID int,
sales_Contract_Code_Name varchar(10),
sales_Number int,
Sales_Money int
)insert into sales_Contract values(1,'100100XS1',100,200)
insert into sales_Contract values(1,'100101XS1',200,400)
insert into sales_Contract values(2,'100101XS2',20 ,100)
insert into sales_Contract values(2,'100100XS2',30 ,150)
godeclare @sql varchar(8000)
set @sql = 'select goods_ID'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then sales_Number end) [sales_Number' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then Sales_Money end) [Sales_Money' + cast(px as varchar) + ']'
from (select distinct px from (select px=(select count(1) from sales_Contract where goods_ID=a.goods_ID and sales_Number<a.sales_Number)+1 , * from sales_Contract a) t) as a
set @sql = @sql + ' from (select px=(select count(1) from sales_Contract where goods_ID=a.goods_ID and sales_Number<a.sales_Number)+1 , * from sales_Contract a) t group by goods_ID'
exec(@sql) drop table sales_Contract/*
goods_ID sales_Number1 Sales_Money1 sales_Number2 Sales_Money2
----------- ------------- ------------ ------------- ------------
1 100 200 200 400
2 20 100 30 150*/
from (
select b.goods_id ,
sum(case left(b.sales_Contract_Code_Name,(charindex('XS',b.sales_Contract_Code_Name)-1)) when '100100' then b.sales_number else 0 end) as sales_number1,
sum(case left(b.sales_Contract_Code_Name,(charindex('XS',b.sales_Contract_Code_Name)-1)) when '100100' then b.sales_money else 0 end) as sales_money1,
sum(case left(b.sales_Contract_Code_Name,(charindex('XS',b.sales_Contract_Code_Name)-1)) when '100101' then b.sales_number else 0 end) as sales_number2,
sum(case left(b.sales_Contract_Code_Name,(charindex('XS',b.sales_Contract_Code_Name)-1)) when '100101' then b.sales_money else 0 end) as sales_money2
from view_sales_contract as b where b.sales_Contract_Code_Name like '%XS%'
group by b.goods_id) as a order by a.goods_id