sql·Ö×é²éѯÎÊÌâ
Çé¾°Ò»£º
±íÖÐÊý¾Ý
name score
aaa 11
aaa 19
bbb 12
bbb 18
ccc 19
ddd 21
ÆÚÍû²éѯ½á¹ûÈçÏÂ
name score
aaa 30
bbb 30
ccc 19
ddd 21
Sql´úÂë
---¼ì²é±íÊÇ·ñ´æÔÚ
if exists(select * from sysobjects where name='testSum')
drop table testSum
go
---´´½¨±í
create table testSum
(
tid int primary key identity(1,1),
tname varchar(30) null,
tscor int null
)
go
insert into testSum (tname,tscor)
select 'aaa',11
union all
select 'aaa',19
union all
select 'bbb',12
union all
select 'bbb',18
union all
select 'ccc',19
union all
select 'ddd',21
---²éѯÓï¾ä
select tname ,sum(tscor) from testSum group by tname
---Ö»²éѯtscor×ܺÍΪ30µÄ
select tname ,sum(tscor) from testSum group by tname having sum(tscor)=30
---¼ì²é±íÊÇ·ñ´æÔÚ
if exists(select * from sysobjects where name='testSum')
drop table testSum
go
---´´½¨±í
create table testSum
(
tid int primary key identity(1,1),
tname varchar(30) null,
tscor int null
)
go
insert into testSum (tname,tscor)
select 'aaa',11
union all
select 'aaa',19
union
Ïà¹ØÎĵµ£º
--SQL ËÙ²éÊÖ²á
/*******************************************/
SELECT
--ÓÃ;£º´ÓÖ¸¶¨±íÖÐÈ¡³öÖ¸¶¨ÁеÄÊý¾Ý
--Óï·¨£º
SELECT column_name(s) from table_name
--Ö÷Òª×Ö¾ä¿ÉժҪΪ:
SELECT select_list [INTO new_table]
from table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING s ......
1.ÏÞÖÆ·µ»Ø¼Ç¼µÄÐÐÊý£¨4-10ÐУ©
select rownum,c1 from t144 where rownum<10
minus
select rownum,c1 from t144 where rownum<5
2.ÅúÁ¿²åÈë
insert into tablename(select * from othertable)
»ò
insert into (column1,column2,,,,,,,)tablename(select column1,column2,,,,,,,from othertable)
3,·Ö×éº¯Ê ......
SQL UNION ²Ù×÷·û
UNION ²Ù×÷·ûÓÃÓںϲ¢Á½¸ö»ò¶à¸ö SELECT Óï¾äµÄ½á¹û¼¯¡£
Çë×¢Ò⣬UNION ÄÚ²¿µÄ SELECT Óï¾ä±ØÐëÓµÓÐÏàͬÊýÁ¿µÄÁС£ÁÐÒ²±ØÐëÓµÓÐÏàËÆµÄÊý¾ÝÀàÐÍ¡£Í¬Ê±£¬Ã¿Ìõ SELECT Óï¾äÖеÄÁеÄ˳Ðò±ØÐëÏàͬ¡£
SQL UNION Óï·¨
SELECT column_name(s) from table_name1
UNION
SELECT column_name(s) from table_na ......
sqlÓïÑÔÖÐÓÐûÓÐÀàËÆCÓïÑÔÖеÄswitch caseµÄÓï¾ä£¿£¿
ûÓÐ,ÓÃcase when À´´úÌæ¾ÍÐÐÁË.
ÀýÈç,ÏÂÃæµÄÓï¾äÏÔʾÖÐÎÄÄêÔÂ
select getdate() as ÈÕÆÚ,case month(get ......
֮ǰʹÓÃPLSQL£¬¶¼ÊÇÒÔдÈëÄÚÈÝΪÖ÷£¬Ð´ÈëÖÐÎÄÄÚÈÝżÓÐÂÒÂ룬µ«ÊÇÖ»ÔÚÏÔʾÉϲ¢Î´Ó°Ïì´æÈ¡£¬ËùÒÔûÓÐÔÚÒâ¡£
½ñÌìÔÚÐÞ¸ÄÒ»¸öÊÓͼʱ£¬ÐèÒª½«ÐÞ¸ÄÄÚÈݼǼÏÂÀ´£¬so,Ïëд¸öÐ޸ļǼ£¬¾Í°ÑÊÓͼÄÚÈÝ¿½±´Á˳öÏÖ£¬·¢ÏÖ¿½±´³öÏÖµÄÖÐÎÄÄÚÈݳöÏÖÂÒÂë¡£
²¢Î´ÍêÈ«ÔÚÒ⣬ÒÔΪֻҪÔÑù·Å»Øplsql¾Í»áºÃµÄ£¬µ«ÊDZ£´æÔÙÈ¡³ö·Åµ½plsqlÖз¢ÏÖÈÔ ......