Sql 查询实例归结

1.Sql 行转列(分组实例)
现有数据如下(每一季度的数据明细):
季度 Fitem  数量
1    A      20
2    B      80
3    A      50
4    B      50

想得到的结果如下(单一ITEM的每季的数量)：

FITEM 1   2  3    4
A     20     50
B         80     50


Select Fitem,
sum(case id when 1 then qty else 0 end) as A1,
sum(case id when 2 then qty else 0 end) as A2,
sum(case id when 3 then qty else 0 end) as A3,
sum(case id when 4 then qty else 0 end) as A4
From BB
Group by Fitem


2.Sql 返回某字段相同两条最小（最新）记录实例
CREATE TABLE A(ID INT IDENTITY(1,1),ITEM NVARCHAR(20))
INSERT A(ITEM) SELECT 'AB'
INSERT A(ITEM) SELECT 'AB'
INSERT A(ITEM) SELECT 'BC'
INSERT A(ITEM) SELECT 'AB'

CREATE TABLE B(ITEM NVARCHAR(20),QTY INT)
INSERT B SELECT 'AB',190
INSERT B SELECT 'BC',100
--SELECT * FROM B

-- 我要得到
-- 同ITEM的只需ID最小的那条记录对应出B表的QTY
-- 如结果中：ITEM=AB的，ID=1的QTY_2=190，ID=2和ID=4的QTY_2=0
方法如下:
--对表A进行分组查询(若相同Item只取最小一条)
--将上个查询和表b进行联接

Select aa.*,B.Qty From A aa
Left join B
ON aa.item=B.item
where id =(Select top 1 id from A Where item=aa.item order by id)

3.将一列多长字符串，拆分成多个记录
-- 将此表和生成只有一列且递增临时表进行结合，找出分隔号",'来进行查询
/*
表 t1,有个字段aaa
ID   aaa
1    a1,a2
2    a2,a4
3    a8
4   
5    a9,a1

需得到结果：
a1
a2
a4
a8
a9
*/
if exists(Select name From sysobjects where [Name]='AA')
 Drop table AA

create TABLE AA(id int,title nvarchar(100))
Insert AA values(3456,'601988,600028,HK3988,HK0386')
Insert AA values(3457,'601988,600028')

-- 生成只有一列且自动递增临时表
select top 8000 id=IDENTITY(int,1,1)
into #T FROM syscolumns a,syscolumns b

Select * From #T

SELECT
    AA.ID,  title=SUBSTRING(AA.title, #T.ID, CHARINDEX(',', AA.title + ',', #T.ID) - #T.ID)
FROM AA, #T
WHERE SUBSTRING(',' + AA.title, #T.id, 1) = ','  
ORDER BY 1,2

Drop table AA
Drop Table #T

4.从主从表中返回数据，子表数据合并例子
----- 返回 main_id main_name  item_name
------     1        报纸        南方报,日报,深圳报

create table main(main_id int primary key,main_name nvarchar(20))
create table Item(Item_id int, main_id int references main(main_id),Item_name nvarchar(20))

insert into main values(1,'报纸')
insert into main values(2,'读物')
insert into main values(3,'玩具')

insert into Item values(1,1,'南方报')
insert into Item values(2,1,'日报')
insert into Item values(3,2,'少年读')
insert into Item values(4,2,'中年读')
insert into Item values(5,3,'深圳报')

方法一：采用建立自定义function
/*
create function fsum(@COL2 nvarchar(100))
returns nvarchar(100)
as
 begin
   declare @var varchar(100)
   set @var=''
   select @var=@var+'/'+Item_name From Item Where

   return (stuff(@var, 1, 1, ''))
 end
*/

select main.main_id,main_name,Item_name
From main
Left join
(
  Select Main_id,Item_name=dbo.fsum(Main_id)
  From Item
  Group by Main_id
)
  as BB

ON (main.main_id=BB.main_id)
Order by main.main_id

方法二:采用存取过程loop合并

create table #tab (main_id int,names text)

declare @main_id int,@item_name nvarchar(20),@main_id_old int
declare @item_names nvarchar(1000)

declare cur cursor for
select main_id,item_name from item ORDER BY main_id

open cur

FETCH NEXT FROM  cur into @main_id,@item_name
SET @main_id_old=@main_id
SET @item_names=''

WHILE (@@fetch_status=0)
BEGIN
    if (@main_id_old=@main_id)
        begin
         set @item_names=@item_names+@item_name+','
        end
    else
       begin
         Insert #tab values (@main_id_old,@item_names)
         SET @main_id_old=@main_id
         SET @item_names=@item_name+','
       end
     print @item_name
    FETCH NEXT FROM  cur into @main_id,@item_name
END

Insert #tab values (@main_id_old,@item_names)

close cur
deallocate cur

--SELECT * FROM #TAB
Select A.*,B.names
From main A LEFT JOIN  #tab B ON  A.main_id=B.main_id

drop table #tab

posted on 2007-01-05 16:09 木子的blog 阅读(467)   

--------------------next---------------------