数据关联问题
有一组数据规则，H=6或H=5.125对应的H1数据是H1=3.5，H> 6对应的H1数据是H1=6，3.75 <=H <6对应的H1数据是2.5，我想把这样的数据关系到表里，应该怎么设计表？怎样关联数据呢，
[解决办法]
这个意思?
select H,H1 =
case
when H = 6 or H = 5.125 then 3.5
when H > 6 then 6
when H > = 3.75 and H < 6 then 2.5
end
INTO NEWTABLE /*插入到新表中*/
from table
[解决办法]
--更新表
update t
set h1=case when H = 6 or H = 5.125 then 3.5
when H > 6 then 6
when H > = 3.75 and H < 6 then 2.5
end
[解决办法]
CREATE TABLE T_TEST(TEST_ID NUMERIC(18,3) NULL,TEST_VALUES NUMERIC(18,3) NULL)
GO
CREATE trigger itr_t_test on t_test
instead of insert
as
begin
select * into #temp_insert from inserted
if exists(select * from #temp_insert where test_id in(6,5.125))
begin
update #temp_insert set test_values=3.5 where test_id in(6,5.125)
end
if exists(select * from #temp_insert where test_id > 6)
begin
update #temp_insert set test_values=6 where test_id > 6
end
if exists(select * from #temp_insert where TEST_ID > 5.125 AND TEST_ID <6 )
begin
update #temp_insert set test_values=2.5 where TEST_ID > 5.125 AND TEST_ID <6
end
if exists(select * from #temp_insert where TEST_ID > = 3.25 AND TEST_ID <5.125)
begin
update #temp_insert set test_values=2.5 where TEST_ID > = 3.25 AND TEST_ID <5.125
end
INSERT INTO t_test(TEST_ID,TEST_VALUES) SELECT TEST_ID ,TEST_VALUES FROM #temp_insert
end
GO
--测试数据
INSERT INTO T_TEST(TEST_ID) VALUES(6)
INSERT INTO T_TEST(TEST_ID) VALUES(5.125)
INSERT INTO T_TEST(TEST_ID) VALUES(7)
INSERT INTO T_TEST(TEST_ID) VALUES(3.26)
INSERT INTO T_TEST(TEST_ID) VALUES(5.5)
SELECT * FROM T_TEST
drop table t_test
drop trigger itr_t_test
