Sql³£¼ûÃæÊÔÌâ ÊÜÓÃÁË
1.
ÓÃÒ»ÌõSQL
Óï¾ä ²éѯ³öÿÃſζ¼´óÓÚ80
·ÖµÄѧÉúÐÕÃû
name kecheng fenshu
ÕÅÈý
ÓïÎÄ 81
ÕÅÈý
Êýѧ 75
ÀîËÄ
ÓïÎÄ 76
ÀîËÄ
Êýѧ 90
ÍõÎå
ÓïÎÄ 81
ÍõÎå
Êýѧ 100
ÍõÎå
Ó¢Óï 90
A: select distinct
name from table where name not in (select distinct name from table where
fenshu<=80)
select name from table group by name having
min(fenshu)>80
2.
ѧÉú±í
ÈçÏÂ:
×Ô¶¯±àºÅ
ѧºÅ
ÐÕÃû ¿Î³Ì±àºÅ ¿Î³ÌÃû³Æ ·ÖÊý
1 2005001
ÕÅÈý 0001
Êýѧ
69
2 2005002
ÀîËÄ 0001
Êýѧ 89
3 2005001
ÕÅÈý 0001
Êýѧ 69
ɾ³ý³ýÁË×Ô¶¯±àºÅ²»Í¬,
ÆäËû¶¼ÏàͬµÄѧÉúÈßÓàÐÅÏ¢
A: delete tablename
where
×Ô¶¯±àºÅ not in(select min(
×Ô¶¯±àºÅ) from tablename group by
ѧºÅ,
ÐÕÃû,
¿Î³Ì±àºÅ,
¿Î³ÌÃû³Æ,
·ÖÊý)
3.
Ò»¸ö½Ð
team
µÄ±í£¬ÀïÃæÖ»ÓÐÒ»¸ö×Ö¶Îname,
Ò»¹²ÓÐ4
Ìõ¼Í¼£¬·Ö±ðÊÇa,b,c,d,
¶ÔÓ¦ËĸöÇò¶Ô£¬ÏÖÔÚËĸöÇò¶Ô½øÐбÈÈü£¬ÓÃÒ»Ìõsql
Óï¾äÏÔʾËùÓпÉÄܵıÈÈü×éºÏ.
ÄãÏÈ°´Äã×Ô¼ºµÄÏë·¨×öһϣ¬¿´½á¹ûÓÐÎÒµÄÕâ¸ö¼òµ¥Âð£¿
´ð£ºselect a.name, b.name
from team a, team b
where a.name <
b.name
4.
ÇëÓÃSQL
Óï¾äʵÏÖ£º´ÓTestDB
Êý¾Ý±íÖвéѯ³öËùÓÐÔ·ݵķ¢Éú¶î¶¼±È101
¿ÆÄ¿ÏàÓ¦Ô·ݵķ¢Éú¶î¸ßµÄ¿ÆÄ¿¡£Çë×¢Ò⣺TestDB
ÖÐÓкܶà¿ÆÄ¿£¬¶¼ÓÐ1
£12
Ô·ݵķ¢Éú¶î¡£
AccID
£º¿ÆÄ¿´úÂ룬Occmonth
£º·¢Éú¶îÔ·ݣ¬DebitOccur
£º·¢Éú¶î¡£
Êý¾Ý¿âÃû£ºJcyAudit
£¬Ê
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
½ñÌ칤×÷時ºò輸Èë數據庫ÖÐÓÐÒ»條數據×Ö¶Î記錄ÊÇ"¿Í戶·ñ¾ö"
ʹÓÃ
select * from TelephoneStatusCategory where CategoryName like '%¿Í戶·ñ%'
select * from TelephoneStatusCategory where CategoryName like '%¿Í戶·ñ¾ö% ......
³£Óô洢¹ý³Ì¼¯½õ,¶¼ÊÇһЩmssql³£ÓõÄһЩ£¬´ó¼Ò¿ÉÒÔ¸ù¾ÝÐèҪѡÔñʹÓá£
¡¡¡¡=================·ÖÒ³==========================
¡¡¡¡/*·ÖÒ³²éÕÒÊý¾Ý*/
¡¡¡¡CREATE PROCEDURE [dbo].[GetRecordSet]
¡¡¡¡@strSql varchar(8000),--²éѯsql,Èçselect * from [user]
¡¡¡¡@PageIndex int,--²éѯµ±Ò³ºÅ
¡¡¡¡@PageSize ......
ijÌìÓиöͬÊÂÎÊÎÒÔõôÔÚÒ»¸ö±íÖвéÕÒij×Ö¶ÎֵΪnull²¢°ÑËüÌ滻ΪÁíÒ»Öµ¡£
¿ªÊ¼ÎÒ¾õµÃÓà select * from ±íÃû where ×Ö¶Î=null Ó¦¸Ã¿ÉÒԵġ£
µ«½á¹ûÊÇûÓб¨´í£¬µ«Ê²Ã´Ò²Ã»ÓÐÏÔʾ³öÀ´¡£
ºóÀ´ÓÖÊÔ£º¡¡
select * from ±íÃû where len(×Ö¶Î)=0 ½á¹ûÈÔÊÇʲôҲûÓÐÏÔʾ¡£
ÎÞÒâÖУ¬Óá¡¡¡select * from ±íÃû wh ......
--Óû§Óë½ÇÉ«¹Øϵ
select a.uid as uid,a.status as uStatus,a.name as uName,
b.uid as rId,b.status as rStatus,b.name as rName
from sysusers a inner join sysusers b on a.gid = b.uid
where a.issqlrole = 0 and a.isapprole = 0 and a.hasdbaccess = 1 and (b.issqlrole = 1 or b.isapprole = 1)
......