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

SQL ÐÐÁл¥×ª

--ÐÐÁл¥×ª
/******************************************************************************************************************************************************
ÒÔѧÉú³É¼¨ÎªÀý×Ó£¬±È½ÏÐÎÏóÒ׶®
ÕûÀíÈË£ºÖйú·ç(Roy)
ÈÕÆÚ:2008.06.06
******************************************************************************************************************************************************/
--1¡¢Ðл¥ÁÐ
--> --> (Roy)Éú³É測試數據
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'ÕÅÈý',N'ÓïÎÄ',78 union all
select N'ÕÅÈý',N'Êýѧ',87 union all
select N'ÕÅÈý',N'Ó¢Óï',82 union all
select N'ÕÅÈý',N'ÎïÀí',90 union all
select N'ÀîËÄ',N'ÓïÎÄ',65 union all
select N'ÀîËÄ',N'Êýѧ',77 union all
select N'ÀîËÄ',N'Ó¢Óï',65 union all
select N'ÀîËÄ',N'ÎïÀí',85
Go
--2000·½·¨:
¶¯Ì¬:
declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
Éú³É¾²Ì¬:
select
    [Student],
    [Êýѧ]=max(case when [Course]='Êýѧ' then [Score] else 0 end),
    [ÎïÀí]=max(case when [Course]='ÎïÀí' then [Score] else 0 end),
    [Ó¢Óï]=max(case when [Course]='Ó¢Óï' then [Score] else 0 end),
    [ÓïÎÄ]=max(case when [Course]='ÓïÎÄ' then [Score] else 0 end)
from
    Class
group by [Student]
GO
¶¯Ì¬:
declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')
Éú³É¾²Ì¬:
select *
from
    Class
pivot
    (max([Score]) for [Course] in([Êýѧ],[ÎïÀí],[Ó¢Óï],[ÓïÎÄ]))b


Ïà¹ØÎĵµ£º

JAVAÁ¬½ÓACCESS£¬SQL Server,MySQL,Oracle

import java.sql.*;
/*
* JAVAÁ¬½ÓACCESS£¬SQL Server,MySQL,OracleÊý¾Ý¿â
*
* */
public class JDBC {
  
public static void main(String[] args)throws Exception {
  
   Connection conn=null;
  
       //====Á¬½ÓACCESSÊý¾Ý¿â ......

ʹÓÃSQLServerÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾ä

Èç¹ûÄã¾­³£Óöµ½ÏÂÃæµÄÎÊÌ⣬Äã¾ÍÒª¿¼ÂÇʹÓÃSQL ServerµÄÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾äÁË£º
SQL³õѧÕß¡£
¾­³£Íü¼Ç³£ÓõÄDML»òÊÇDDL SQL Óï¾ä¡£
ÔÚ¶àÈË¿ª·¢Î¬»¤µÄSQLÖУ¬Ã¿¸öÈ˶¼ÓÐ×Ô¼ºµÄSQLÏ°¹ß£¬Ã»ÓÐÒ»Ì×ͳһµÄ¹æ·¶¡£
ÔÚSQL Server Management StudioÖУ¬ÒѾ­¸ø´ó¼ÒÌṩÁ˺ܶೣÓõÄÏÖ³ÉSQL¹æ·¶Ä£°å¡£
SQL Server Management ......

aspµ÷ÓÃsql server´æ´¢¹ý³Ì

ASPµ÷Óô洢¹ý³ÌµÄ·½·¨£º    
 
----  ¡¡¡¡µ÷Óô洢¹ý³ÌµÄ·½·¨ÐèҪʹÓÃADOµÄCommand¶ÔÏó¡£Command¶ÔÏóÊǶÔÒ»¸öÊý¾ÝÔ´ÔËÐÐÌض¨ÃüÁîµÄ¶¨Ò壨ÀýÈ磬һ¸öSQL²éѯ»òÒ»¸öSQL´æ´¢¹ý³Ì£©¡£ÏÂÃæÒÔMS  SQL  SERVER6.5Ϊºǫ́Êý¾Ý¿â£¬½éÉÜASPµ÷Óô洢¹ý³ÌµÄ·½·¨¡£    
 
----  ¡ ......

sql ·ÖÒ³¼¼Êõ

·ÖÒ³Óï¾ä
sqlserver ·½°¸1: select top 10 * from t where id not in(select top 30 id from t order by id) order by id
·½°¸2£º select top 10 * from t where id in (select top 40 id from t order by id)oder by id desc
mysql: select * from t order by id limit 30,10
oracle: select * from (select rownu ......

SQLÁ·Ï°

father±í   son±í    
fid fname   sid sname fid height money
1 a   100 s1 1 1.7 7000
2 b   101 s2 2 1.6 8000
3 c   102&nbs ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ