SELECT[Id],[Name],[Comment]FROM[Product1]WHERELEN([Name]) > 5 UNION SELECT[Id],[Name],[Comment]FROM[Product2]WHERE[Id]IN (11,20) AND[Comment]IS NOT NULL
SELECTTOP N [Id],[Name],[Comment]FROM[Product1] UNION
SELECTTOP N [Id],[Name],[Comment]FROM[Product2]
This is so easy!但是假如您希望从包含Type字段的某表中根据Type分别随机筛选N条记录并将结果合并成一张表,您可能会像下面这样写:
SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE1'ORDERBYNEWID() UNION SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE2'ORDERBYNEWID() UNION SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE3'ORDERBYNEWID() UNION SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE4'ORDERBYNEWID() UNION SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE5'ORDERBYNEWID() UNION SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE6'ORDERBYNEWID() UNION SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE7'ORDERBYNEWID()
SELECT*FROM (SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE1'ORDERBYNEWID()) AS[Product1] UNION SELECT*FROM (SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE2'ORDERBYNEWID()) AS[Product2] UNION SELECT*FROM (SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE3'ORDERBYNEWID()) AS[Product3] UNION SELECT*FROM (SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE4'ORDERBYNEWID()) AS[Product4] UNION SELECT*FROM (SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE5'ORDERBYNEWID()) AS[Product5] UNION SELECT*FROM (SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE6'ORDERBYNEWID()) AS[Product6] UNION SELECT*FROM (SELECTTOP N [Id],[Name],[Comment]FROM[Product]WHERE[Type]='TYPE7'ORDERBYNEWID()) AS[Product7]