¡¾×ª¡¿ sqlͳ¼Æ£¹ØÓÚѧÉú³É¼¨(´ð°¸)
sqlͳ¼Æ£¹ØÓÚѧÉú³É¼¨(´ð°¸)
http://blog.sina.com.cn/s/blog_61380b320100ej9p.html
´ð°¸£º
1. ¼ÆËãÿ¸öÈ˵Ä×ܳɼ¨²¢ÅÅÃû
select name,sum(score) as allscore from stuscore group by name order by allscore
2. ¼ÆËãÿ¸öÈ˵Ä×ܳɼ¨²¢ÅÅÃû
select distinct t1.name,t1.stuid,t2.allscore from stuscore t1,
(
select stuid,sum(score) as allscore from stuscore group by stuid
)t2
where t1.stuid=t2.stuid
order by t2.allscore desc
3. ¼ÆËãÿ¸öÈ˵¥¿ÆµÄ×î¸ß³É¼¨
select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,
(
select stuid,max(score) as maxscore from stuscore group by stuid
) t2
where t1.stuid=t2.stuid and t1.score=t2.maxscore
4.¼ÆËãÿ¸öÈ˵Äƽ¾ù³É¼¨
select distinct t1.stuid,t1.name,t2.avgscore from stuscore t1,
(
select stuid,avg(score) as avgscore from stuscore group by stuid
) t2
where t1.stuid=t2.stuid
5.Áгö¸÷Ãſγ̳ɼ¨×îºÃµÄѧÉú
select t1.stuid,t1.name,t1.subject,t2.maxscore from stuscore t1,
(
select subject,max(score) as maxscore from stuscore group by subject
) t2
where t1.subject=t2.subject and t1.score=t2.maxscore
6.Áгö¸÷Ãſγ̳ɼ¨×îºÃµÄÁ½Î»Ñ§Éú
select distinct t1.* from stuscore t1
where t1.stuid in
(select top 2 stuscore.stuid from stuscore where subject = t1.subject order by score desc)
order by t1.subject
7.ѧºÅ ÐÕÃû ÓïÎÄ Êýѧ Ó¢Óï ×Ü·Ö Æ½¾ù·Ö
select&nbs
Ïà¹ØÎĵµ£º
select upper(name) from syscolumns
where id =any
(
select Id from sysobjects where name='tb_city'
)
lower
select row_number() over(order by firmid) from dbo.tb_BuildingMaterial
Éú³É ×ÔÔö 123
select rank() over(order by firmid) from dbo.tb_BuildingMaterial
Éú³É×ÔÔö123 ¸ù¾ÝÅ ......
Ò»¡¢¼òµ¥²éѯ
¡¡¡¡ ¼òµ¥µÄTransact-SQL²éѯֻ°üÀ¨Ñ¡ÔñÁÐ±í¡¢from×Ó¾äºÍWHERE×Ӿ䡣
ËüÃÇ·Ö±ð˵Ã÷Ëù²éѯÁС¢²éѯµÄ
±í»òÊÓͼ¡¢ÒÔ¼°ËÑË÷Ìõ¼þµÈ¡£
ÀýÈ磬ÏÂÃæµÄÓï¾ä²éѯtesttable±íÖÐÐÕÃûΪ“ÕÅÈý”µÄnickname×ֶκÍemail×ֶΡ£
SELECT nickname,email
from testtable WHERE name='ÕÅÈý'
(Ò»)Ñ¡ÔñÁбí
¡ ......
µÚÒ»ÖÖ£º
SELECT
CASE
WHEN
price IS NULL THEN
'Not yet priced'
WHEN
price < 10 THEN
'Very Reasonable Title'
WHEN
price >= 10 AND
price < 20 THEN
'Coffee Table Title'
EL ......
JavaÊý¾ÝÀàÐÍ
HibernateÊý¾ÝÀàÐÍ
±ê×¼SQLÊý¾ÝÀàÐÍ
(PS:¶ÔÓÚ²»Í¬µÄDB¿ÉÄÜÓÐËù²îÒì)
byte¡¢java.lang.Byte
byte
TINYINT
short¡¢java.lang.Short
short
SMALLINT
int¡¢java.lang.Integer
integer
INGEGER
long¡¢java.lang.Long
long
BIGINT
float¡¢java.lang.Float
float
FLOAT
double¡¢java.lang.Doubl ......
SQL¸ß¼¶Ê¹ÓÃ
SELECT¸ß¼¶²éѯ
´íÎó´¦Àí
ÊÂÎñ´¦Àí
Êý¾ÝµÄËø¶¨
Óαê
1.SELECT¸ß¼¶²éѯ
Ç°ÃæÒѾ½éÉÜÁËSELECTÓï¾äµÄÓ÷¨£¬±¾½ÚÖ÷Òª½éÉÜÊý¾Ý»ã×Ü¡¢Áª½Ó²éѯ¡¢×Ó²éѯºÍ¹Ø¼ü´ÊUNIONµÄʹÓá£
(1)Êý¾Ý»ã×Ü
Ϊ¾ö²ßÖ§³ÖϵͳÉú³É¾ÛºÏÊÂÎñµÄ»ã×ܱ¨±íÊÇÒ»ÏÔÓ²¢ÇÒÏ൱ÏûºÄ×ÊÔ´µÄ¹¤×÷¡£SQL Server 2000ÌṩÁ½¸öÁé»îÇÒÇ¿´óµÄ×é¼þ ......