表里面有这样的纪录,怎么建表:id date name
1 2006/12/1 aaa
1 2006/12/1 bbb
2 2006/12/2 aaa
2 2006/12/2 bbb
3 2006/12/3 aaa
3 2006/12/3 bbb
。
。
。
也就是说id 是绑定date和name自增长
1 2006/12/1 aaa
1 2006/12/1 bbb
2 2006/12/2 aaa
2 2006/12/2 bbb
3 2006/12/3 aaa
3 2006/12/3 bbb
。
。
。
也就是说id 是绑定date和name自增长
不明白?你是说id是由date的几号决定的吗?
GO-- 用触发器
CREATE TRIGGER tr_test ON tb
INSTEAD OF INSERT
AS
IF @@ROWCOUNT = 0
RETURN
SELECT A_id = IDENTITY(int,1,1), * INTO # FROM inserted ORDER BY date, name
UPDATE A SET
id = A.A_id - B.A_id + 1
+ ISNULL((SELECT MAX(id) FROM tb WHERE date = A.date AND name = A.name), 0)
FROM # A,(
SELECT A_id = MIN(A_id), date, name
FROM #
GROUP BY date, name
)B
WHERE A.date = B.date
AND A.name = B.nameINSERT tb(id, date, name)
SELECT id, date, name FROM #
GOINSERT tb(date, name)
SELECT '2006/12/1', 'aaa' UNION ALL
SELECT '2006/12/1', 'bbb' UNION ALL
SELECT '2006/12/2', 'aaa' UNION ALL
SELECT '2006/12/2', 'bbb' UNION ALL
SELECT '2006/12/3', 'aaa' UNION ALL
SELECT '2006/12/3', 'bbb'SELECT * FROM tb
GODROP TABLE tb-- 结果:
id date name
1 2006-12-01 00:00:00.000 aaa
1 2006-12-01 00:00:00.000 bbb
1 2006-12-02 00:00:00.000 aaa
1 2006-12-02 00:00:00.000 bbb
1 2006-12-03 00:00:00.000 aaa
1 2006-12-03 00:00:00.000 bbb
CREATE TABLE tb(id int, date datetime, name varchar(10))
GO-- 用触发器
CREATE TRIGGER tr_test ON tb
INSTEAD OF INSERT
AS
IF @@ROWCOUNT = 0
RETURN
SELECT A_id = IDENTITY(int,1,1), * INTO # FROM inserted ORDER BY name
UPDATE A SET
id = A.A_id - B.A_id + 1
+ ISNULL((SELECT MAX(id) FROM tb WHERE name = A.name), 0)
FROM # A,(
SELECT A_id = MIN(A_id), name
FROM #
GROUP BY name
)B
WHERE A.name = B.nameINSERT tb(id, date, name)
SELECT id, date, name FROM #
GOINSERT tb(date, name)
SELECT '2006/12/1', 'aaa' UNION ALL
SELECT '2006/12/1', 'bbb' UNION ALL
SELECT '2006/12/2', 'aaa' UNION ALL
SELECT '2006/12/2', 'bbb' UNION ALL
SELECT '2006/12/3', 'aaa' UNION ALL
SELECT '2006/12/3', 'bbb'SELECT * FROM tb
GODROP TABLE tb-- 结果:
id date name
1 2006-12-01 00:00:00.000 aaa
2 2006-12-02 00:00:00.000 aaa
3 2006-12-03 00:00:00.000 aaa
1 2006-12-03 00:00:00.000 bbb
2 2006-12-02 00:00:00.000 bbb
3 2006-12-01 00:00:00.000 bbb
id = A.A_id - B.A_id + 1
有作用? CREATE TRIGGER tr_test ON tb
INSTEAD OF INSERT
AS
IF @@ROWCOUNT = 0
RETURN
SELECT * INTO # FROM inserted ORDER BY nameUPDATE A SET
id = 1 //--------------
+ ISNULL((SELECT MAX(id) FROM tb WHERE name = A.name), 0)
FROM # A,(
SELECT A_id = MIN(A_id), name
FROM #
GROUP BY name
)B
WHERE A.name = B.name
SELECT ISNULL(MAX(id),0)+1 AS id,
'date_value' AS date, 'name_value' AS name
FROM table1
WHERE date='date_value'
AND name='name_value'
有这样的数据:id date userName
1 2006/12/1 李亚鹏
2 2006/12/1 李亚鹏
3 2006/12/1 李亚鹏
1 2006/12/2 李亚鹏
2 2006/12/2 李亚鹏
3 2006/12/2 李亚鹏
1 2006/12/2 王非
2 2006/12/2 王非
3 2006/12/2 王非
4 2006/12/2 王非
。
。
。