--1. 按拼音排序 DECLARE @t TABLE(col varchar(2)) INSERT @t SELECT '中' UNION ALL SELECT '国' UNION ALL SELECT '人'SELECT * FROM @t ORDER BY col COLLATE Chinese_PRC_CS_AS_KS_WS /*--结果 col ---- 国 人 中 --*/ GO /*==========================================*/ --2. 汉字首字母查询处理用户定义函数 CREATE FUNCTION f_GetPY(@str nvarchar(4000)) RETURNS nvarchar(4000) AS BEGIN DECLARE @py TABLE( ch char(1), hz1 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS, hz2 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS) INSERT @py SELECT 'A',N'吖',N'鏊' UNION ALL SELECT 'B',N'八',N'簿' UNION ALL SELECT 'C',N'嚓',N'错' UNION ALL SELECT 'D',N'哒',N'跺' UNION ALL SELECT 'E',N'屙',N'贰' UNION ALL SELECT 'F',N'发',N'馥' UNION ALL SELECT 'G',N'旮',N'过' UNION ALL SELECT 'H',N'铪',N'蠖' UNION ALL SELECT 'J',N'丌',N'竣' UNION ALL SELECT 'K',N'咔',N'廓' UNION ALL SELECT 'L',N'垃',N'雒' UNION ALL SELECT 'M',N'妈',N'穆' UNION ALL SELECT 'N',N'拿',N'糯' UNION ALL SELECT 'O',N'噢',N'沤' UNION ALL SELECT 'P',N'趴',N'曝' UNION ALL SELECT 'Q',N'七',N'群' UNION ALL SELECT 'R',N'蚺',N'箬' UNION ALL SELECT 'S',N'仨',N'锁' UNION ALL SELECT 'T',N'他',N'箨' UNION ALL SELECT 'W',N'哇',N'鋈' UNION ALL SELECT 'X',N'夕',N'蕈' UNION ALL SELECT 'Y',N'丫',N'蕴' UNION ALL SELECT 'Z',N'匝',N'做' DECLARE @i int SET @i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str) WHILE @i>0 SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1),ch) ,@i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str) FROM @py WHERE SUBSTRING(@str,@i,1) BETWEEN hz1 AND hz2 RETURN(@str) END GO
抱歉啊,abcd是歌手字母索引字段,值是字母A--Z中的任意一个
select id as 歌曲编号, musicname as 歌曲名, singer as 歌手名 from musiclist m where exists(select 1 from nclass n where n.nclassid=m.nclassid and n.abcd='A') ?
在树哥的语句上加上 order by singer
如果abcd存的单个的a,b,c,d...那查b类的就只要作内联接就行了, select *from musiclist a inner join nclass b on a.nclassid=b.nclassid and b.索引='B'如果abcd以a-b,c-f等方式存储,比如查B类的,用下面的 select * from musiclist m where exists(select 1 from nclass n where n.nclassid=m.nclassid and patindex('%['+abcd+']%','B' )>0)
5楼的代码可以啊 字段abcd存放26个字母中的任意一个,有重复 这句 exists(select 1 from nclass n where n.nclassid=m.nclassid and n.abcd='A')select 1 是啥意思,咋没见过这用法
DECLARE @t TABLE(col varchar(2))
INSERT @t SELECT '中'
UNION ALL SELECT '国'
UNION ALL SELECT '人'SELECT * FROM @t ORDER BY col COLLATE Chinese_PRC_CS_AS_KS_WS
/*--结果
col
----
国
人
中
--*/
GO
/*==========================================*/
--2. 汉字首字母查询处理用户定义函数
CREATE FUNCTION f_GetPY(@str nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @py TABLE(
ch char(1),
hz1 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS,
hz2 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS)
INSERT @py SELECT 'A',N'吖',N'鏊'
UNION ALL SELECT 'B',N'八',N'簿'
UNION ALL SELECT 'C',N'嚓',N'错'
UNION ALL SELECT 'D',N'哒',N'跺'
UNION ALL SELECT 'E',N'屙',N'贰'
UNION ALL SELECT 'F',N'发',N'馥'
UNION ALL SELECT 'G',N'旮',N'过'
UNION ALL SELECT 'H',N'铪',N'蠖'
UNION ALL SELECT 'J',N'丌',N'竣'
UNION ALL SELECT 'K',N'咔',N'廓'
UNION ALL SELECT 'L',N'垃',N'雒'
UNION ALL SELECT 'M',N'妈',N'穆'
UNION ALL SELECT 'N',N'拿',N'糯'
UNION ALL SELECT 'O',N'噢',N'沤'
UNION ALL SELECT 'P',N'趴',N'曝'
UNION ALL SELECT 'Q',N'七',N'群'
UNION ALL SELECT 'R',N'蚺',N'箬'
UNION ALL SELECT 'S',N'仨',N'锁'
UNION ALL SELECT 'T',N'他',N'箨'
UNION ALL SELECT 'W',N'哇',N'鋈'
UNION ALL SELECT 'X',N'夕',N'蕈'
UNION ALL SELECT 'Y',N'丫',N'蕴'
UNION ALL SELECT 'Z',N'匝',N'做'
DECLARE @i int
SET @i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)
WHILE @i>0
SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1),ch)
,@i=PATINDEX('%[吖-做]%' COLLATE Chinese_PRC_CS_AS_KS_WS,@str)
FROM @py
WHERE SUBSTRING(@str,@i,1) BETWEEN hz1 AND hz2
RETURN(@str)
END
GO
id as 歌曲编号,
musicname as 歌曲名,
singer as 歌手名
from
musiclist m
where
exists(select 1 from nclass n where n.nclassid=m.nclassid and n.abcd='A')
?
select *from musiclist a inner join nclass b on a.nclassid=b.nclassid and b.索引='B'如果abcd以a-b,c-f等方式存储,比如查B类的,用下面的
select * from musiclist m where exists(select 1 from nclass n where n.nclassid=m.nclassid and patindex('%['+abcd+']%','B' )>0)
字段abcd存放26个字母中的任意一个,有重复
这句
exists(select 1 from nclass n where n.nclassid=m.nclassid and n.abcd='A')select 1 是啥意思,咋没见过这用法