Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

sqlÖг£Óö¯Ì¬µÄÐÐתÁÐ

//È¡³öÔ´Êý¾Ý
select groupname,totalnum,inputdate,groupid into #temp from
 (select count(*)as totalnum,p.groupid,g.groupname,convert(nvarchar(10),inputdate,120) as 'inputdate'
 from person p left join admin_group g on p.groupid = g.groupid and deleteflag = '0'
 where p.inactive='0' 
 group by p.groupid,g.groupname,convert(nvarchar(10),p.inputdate,120)) a
 left join
 (select a.groupid as areagroupid,b.groupname as area,b.areaid
 from admin_group a inner join admin_group b on a.upperid = b.groupid) b on a.groupid=b.areagroupid
//µÚÒ»ÖÖ·½Ê½Äܹ»¿ØÖÆÏÔʾµÄÊý¾ÝµÄÑùʽ£¬ËٶȱȽϿì
declare @s nvarchar(max)
select @s = 'select groupname as ÉçÇøÃû,groupid'
select @s = @s + ',isnull(max(case when inputdate=''' + cast(inputdate as varchar(10)) + ''' then totalnum end),0) as ''' + cast(inputdate as varchar(10)) + ''''  from #temp group by inputdate order by inputdate desc
select @s = @s + ' from #temp where groupid=71 group by groupname,groupid'
exec(@s)
//µÚ¶þÖÖʹÓÃpivot£¨sql2005ÒÔÉÏ°æ±¾ÖÐÓУ©
declare @s nvarchar(max)
Select     @s=isnull(@s+',','')+quotename(inputdate) from #temp group by inputdate order by inputdate desc
exec('select * from #temp pivot (max(totalnum) for inputdate in('+@s+'))b')


Ïà¹ØÎĵµ£º

SQL Êý¾Ý¿âÖ®Ò»

1.      SELECT
ʵÀý105
SELECT ID "±àºÅ",Name ÐÕÃû,
       Math_Score 'Êýѧ³É¼¨',  //ÔõôÓеÄÓÐAS,ÓеÄûÓÐ
       Music_Score AS ÒôÀֳɼ¨,
       English_Score AS Ó¢Îijɼ¨
f ......

SQLÊý¾Ý¿âÖ®¶þ

l  INNER JOIN
ÄÚÁ¬½ÓÊÇ×î³£¼ûµÄÒ»ÖÖÁ¬½Ó£¬ËüÒ³±»³ÆΪÆÕͨÁ¬½Ó£¬¶øE.FCodd×îÔç³Æ֮Ϊ×ÔÈ»Á¬½Ó¡£
ÏÂÃæÊÇANSI SQL£­92±ê×¼
select *  from    t_institution i
inner join t_teller t
on i.inst_no = t.inst_no  //˵Á½¸ö±íÖ®¼äµÄ¹ØϵÓÃON
where i.inst_no = "5801"
ÆäÖÐinner¿ÉÒÔÊ¡Â ......

oracleÊý¾Ý¿âµ¼Èëµ½sql server2005

ÒýÓÃ:http://blog.csdn.net/wizardlun/archive/2009/09/08/4531576.aspx
½ñÌ죬ÖÕÓÚ°ÑoracleÖеÄÊý¾Ý¿âµ¼Èëµ½sql serverÖÐÁË£¬·½·¨ÈçÏ£º
  Ò»¡¢ÔÚsql serverÖн¨¸öͬÃûÊý¾Ý¿â£¬ÀýÈçssdb¡£
  ¶þ¡¢ÓÒ¼üµã»÷ssdb,Ñ¡Ôñ“ËùÓÐÈÎÎñ”——>“µ¼ÈëÊý¾Ý”£¬¾Í»áµ¯³öÒ»¸ö“DTS ......

Ò»¸ösql¼òµ¥º¯ÊýʵÏÖÆ´½Ó×Ö·û´®

--²âÊÔÊý¾Ý
create table table1(AID int,NAME nvarchar(20))
create table table2 (BID int,NUMBER nvarchar(20))
insert into table1 select 1,'Tom' union all
select 2,'Jim'
insert into table2 select 1,20 union all
select 1,30
--º¯Êý
create function F_Str(@ID int)
returns nvarchar(100)
as
begin ......

sqlÓï¾ä ½Øȡʱ¼ä Ö»ÏÔʾ Ä꣬Ô£¬Èյĸñʽ


Õª×Ôhttp://blog.sina.com.cn/zhm85
SQLÓï¾ä½Øȡʱ¼ä£¬Ö»ÏÔʾÄêÔÂÈÕ£¨2004-09-12£©
select CONVERT(varchar, getdate(), 120 )
‘getdate£¨£©’¸ÄΪʱ¼ä×Ö¶ÎÃû‘createtime’
ÔÙÖØÃüÃûмÓÁУ¨Select Name AS UName from Users£©
ÀýÈç select convert(varchar(11),createtime,120) as Ndate fro ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ