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
Ïà¹ØÎĵµ£º
import java.sql.*;
/*
* JAVAÁ¬½ÓACCESS£¬SQL Server,MySQL,OracleÊý¾Ý¿â
*
* */
public class JDBC {
public static void main(String[] args)throws Exception {
Connection conn=null;
//====Á¬½ÓACCESSÊý¾Ý¿â ......
Èç¹ûÄã¾³£Óöµ½ÏÂÃæµÄÎÊÌ⣬Äã¾ÍÒª¿¼ÂÇʹÓÃSQL ServerµÄÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾äÁË£º
SQL³õѧÕß¡£
¾³£Íü¼Ç³£ÓõÄDML»òÊÇDDL SQL Óï¾ä¡£
ÔÚ¶àÈË¿ª·¢Î¬»¤µÄSQLÖУ¬Ã¿¸öÈ˶¼ÓÐ×Ô¼ºµÄSQLÏ°¹ß£¬Ã»ÓÐÒ»Ì×ͳһµÄ¹æ·¶¡£
ÔÚSQL Server Management StudioÖУ¬ÒѾ¸ø´ó¼ÒÌṩÁ˺ܶೣÓõÄÏÖ³ÉSQL¹æ·¶Ä£°å¡£
SQL Server Management ......
ASPµ÷Óô洢¹ý³ÌµÄ·½·¨£º
---- ¡¡¡¡µ÷Óô洢¹ý³ÌµÄ·½·¨ÐèҪʹÓÃADOµÄCommand¶ÔÏó¡£Command¶ÔÏóÊǶÔÒ»¸öÊý¾ÝÔ´ÔËÐÐÌض¨ÃüÁîµÄ¶¨Ò壨ÀýÈ磬һ¸öSQL²éѯ»òÒ»¸öSQL´æ´¢¹ý³Ì£©¡£ÏÂÃæÒÔMS SQL SERVER6.5Ϊºǫ́Êý¾Ý¿â£¬½éÉÜASPµ÷Óô洢¹ý³ÌµÄ·½·¨¡£
---- ¡ ......
·ÖÒ³Óï¾ä
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 ......
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 ......