请问,你们是怎么创建用户帐号的

[水如烟]

我这里的是:
用户名使用全拼表示，如“张小明”，则用“zhangxiaoming”表示。若出现用户名重名时，依次按照如下原则进行调整：
1） 在姓名最后一个字拼音前添加下划线；
2） 在姓名倒数第二个字拼音前添加下划线；
3） 在姓名的所有字拼音之间添加下划线；
4） 把姓名最后一个字的全拼简化为首字母；
5） 把姓名最后倒数第二个字的全拼简化为首字母；
6） 把姓名所有字的全拼简化为首字母。
根据上述原则，用户张小明的用户名出现重名时，用户名的调整先后顺序为：“zhangxiaoming”、“zhangxiao_ming”、“zhang_xiaoming”、“zhang_xiao_ming”、“zhangxiaom”、“zhangxiao_m”、“zhang_xiaom”、“zhang_xiao_m”、“zhangxm”、“zhangx_m”、“zhang_xm”、“zhang_x_m”、“zhxm”等，如此类推。

[liangCK]

晕..
看错了..
这标题起得不好.

[dobear_0922]

小梁速度真快，，，

[utpcb]

晕!
名字你允许重复吧在加个工号　
或者个别的什么字段限定唯一植吧

[水如烟]

根据这个规则,我做了一个函数,只是排序就不好按规则指定的顺序排了

DECLARE @UserName nvarchar(20)
SET @userName = N'张小明'

SELECT * FROM [LzmtwWorks].[Helper].[UserIDList] (@UserName)

结果:

ID   UserID
---- --------------------------
1    zhangxiaoming
2    zhangxiao_ming
3    zhang_xiaoming
4    zhxiaoming
5    zhang_xiao_ming
6    zh_xiaoming
7    zhxiao_ming
8    zhangxiaom
9    zhangxming
10   zh_xiao_ming
11   zhang_xiaom
12   zhang_xming
13   zhangx_ming
14   zhangxiao_m
15   zhxiaom
16   zhxming
17   zhang_x_ming
18   zhang_xiao_m
19   zh_xiaom
20   zh_xming
21   zhx_ming
22   zhxiao_m
23   zhangxm
24   zh_x_ming
25   zh_xiao_m
26   zhangx_m
27   zhang_xm
28   zhxm
29   zhang_x_m
30   zh_xm
31   zhx_m
32   zh_x_m

(32 行受影响)

[水如烟]

我的函数是(拼音部分略去)

USE [LzmtwWorks]
GO
-- ===============================================================
-- Author:LzmTW
-- Create date:20080123
-- Description:根据中文名字创建用户列表。
-- @UserName:中文名字。两到四个汉字组成，否则视为无效。
-- ===============================================================
CREATE FUNCTION [Helper].[UserIDList]
(
	@UserName nvarchar(20)
)
RETURNS 
@Table  TABLE 
(
	 [ID] tinyint identity(1,1) primary key 
	,[UserID] varchar(200)
)
AS
BEGIN
	IF @UserName IS NULL
		RETURN

	SET @UserName = REPLACE(@UserName, N' ', N'')

	DECLARE 
		 @Count tinyint

	SET @Count = LEN(@UserName)

	IF NOT @Count BETWEEN 2 AND 4
		RETURN

	DECLARE @Table0 TABLE 
	(
		 [ID] int PRIMARY KEY
		,[Char] nchar(1)
		,[Unicode] int
	)

	DECLARE 
		 @TmpCount tinyint
		,@xml xml

	SET @xml = [Chars].[xml](@UserName)

	INSERT INTO @Table0
	SELECT *
	FROM [Chars].[xmlTable](@xml)

	SELECT @TmpCOunt = COUNT(*) FROM @Table0 WHERE [Unicode] > 12294

	IF @Count <> @TmpCount
		RETURN

	DECLARE @Table1 TABLE([ID] tinyint primary key, [V1] nvarchar(20), [V2] nvarchar(20), [V3] nvarchar(20), [V4] nvarchar(20))

	INSERT @Table1([ID]) VALUES(1)
	INSERT @Table1([ID]) VALUES(2)
	INSERT @Table1([ID]) VALUES(3)
	INSERT @Table1([ID]) VALUES(4)

	DECLARE 
		 @IsValid bit
		,@Index tinyint
		,@Char nchar(1)
		,@CharPinyin varchar(10)
		,@Vowel varchar(2)

	SET @IsValid = 1
	SET @Index = 1

	WHILE @Index <= @Count
	BEGIN
		SELECT @Char = [Char] FROM @Table0 WHERE [ID] = @Index
		SELECT @CharPinyin = [Pinyin] FROM [Chinese].[CharPinyin](@Char)
	
		IF @CharPinyin IS NULL
		BEGIN
			SET @IsValid = 0
			GOTO NOTVALID
		END

		SET @Vowel = CASE WHEN SUBSTRING(@CharPinyin, 2, 1) = 'h' THEN LEFT(@CharPinyin, 2) ELSE LEFT(@CharPinyin, 1) END

		IF @Index = 1
		BEGIN
			UPDATE @Table1
			SET [V1] = @CharPinyin
			WHERE [ID] = 1

			UPDATE @Table1
			SET [V1] = @Vowel
			WHERE [ID] = 2
		END

		IF @Index = 2
		BEGIN
			UPDATE @Table1
			SET [V2] = @CharPinyin
			WHERE [ID] = 1

			UPDATE @Table1
			SET [V2] = N'_' + @CharPinyin
			WHERE [ID] = 2

			UPDATE @Table1
			SET [V2] = @Vowel
			WHERE [ID] = 3

			UPDATE @Table1
			SET [V2] = N'_' + @Vowel
			WHERE [ID] = 4
		END

		IF @Index = 3
		BEGIN
			UPDATE @Table1
			SET [V3] = @CharPinyin
			WHERE [ID] = 1

			UPDATE @Table1
			SET [V3] = N'_' + @CharPinyin
			WHERE [ID] = 2

			UPDATE @Table1
			SET [V3] = @Vowel
			WHERE [ID] = 3

			UPDATE @Table1
			SET [V3] = N'_' + @Vowel
			WHERE [ID] = 4
		END

		IF @Index = 4
		BEGIN
			UPDATE @Table1
			SET [V4] = @CharPinyin
			WHERE [ID] = 1

			UPDATE @Table1
			SET [V4] = N'_' + @CharPinyin
			WHERE [ID] = 2

			UPDATE @Table1
			SET [V4] = @Vowel
			WHERE [ID] = 3

			UPDATE @Table1
			SET [V4] = N'_' + @Vowel
			WHERE [ID] = 4
		END

		SET @Index = @Index + 1
	END

NOTVALID:
	IF @IsValid = 0
		RETURN
	
	DECLARE @Table2 TABLE (UserID varchar(50) primary key, Num tinyint)

	DECLARE @Table3 TABLE (UserID varchar(50) ,Num tinyint)

	INSERT INTO @Table3
	SELECT DISTINCT 
		  UserID = a.[V1] + b.[V2] + ISNULL(c.[V3], '') + ISNULL(d.[V4], '')
		 ,Num = a.[ID] + b.[ID] + ISNULL(c.[ID], 0) + ISNULL(d.[ID], 0)
	FROM @Table1 a CROSS JOIN @Table1 b CROSS JOIN @Table1 c CROSS JOIN @Table1 d
	WHERE NOT a.[V1] IS NULL

	INSERT INTO @Table2(UserID)
	SELECT DISTINCT UserID FROM @Table3

	UPDATE @Table2
	SET Num = b.Num
	FROM @Table2 a
	INNER JOIN
	(
		SELECT a.UserID, MIN(a.Num) Num FROM @Table3 a INNER JOIN @Table2 b ON a.UserID = b.UserID GROUP BY a.[UserID]
	) b
	ON a.UserID = b.UserID

	INSERT INTO @Table
	SELECT UserID
	FROM @Table2
	ORDER BY num, SOUNDEX (UserID) DESC, LEN(UserID) DESC
	
	RETURN 
END

[dobear_0922]

支持一下，，，

[liangCK]

厉害.
关注.

[-狙击手-]

楼主分真分，我接

[mengmou]

我怎么觉得楼主整天在研究些没用的？我以前也这样，现在不了。

[fqbnet2050]

好贴，学习一下

[chuifengde]

呵呵，赞一个

[水如烟]

怎么没用呢,我的域管理中用到.帐号不许是中文名.

如果你要做企业数据仓库的话，我以前“研究”的全是基础，必要的。

[-狙击手-]

恩

[水如烟]

切，转回自己地头删了这些，格式太臭了。

[wzy_love_sly]

叼...

[中国风]

哦

[areswang]

1

[yesyesyes]

嵌入下划线不好,不如加有意义的后缀

[areswang]

1

[水如烟]

加下画线可以分辨,它也能够应用到邮箱名称中