declare @鱼 float ,@肉 float ,@葱 float,@x int ,@y int,@z int
select @鱼=3.5,@肉=6,@葱=0.1
select @x=1,@y=1,@z=1
while @x<=cast(100/@鱼 as int)
begin
if exists(select * from (select id=(@x+@y+@z))x where id=100) and exists(select * from (select id=(@鱼*@x+@肉*@y+@葱*@z))x where id=100)
begin
print '鱼'+cast(@x as varchar(20))+'肉'+cast( @y as varchar(20))+'葱'+cast(@z as varchar(20))
end
if not exists(select * from (select id=(@x+@y+@z))x where id=100) or not exists(select * from (select id=(@鱼*@x+@肉*@y+@葱*@z))x where id=100)
begin
set @y=2
while @y<=cast(100/@肉 as int)
begin
set @z=100-@x-@y
if exists(select * from (select id=(@x+@y+@z))x where id=100) and exists(select * from (select id=(@鱼*@x+@肉*@y+@葱*@z))x where id=100)
begin
print '鱼'+cast(@x as varchar(20))+'肉'+cast( @y as varchar(20))+'葱'+cast(@z as varchar(20))
end
set @y=@y+1
end
end
set @x=@x+1
end
1.在ms_sql实现 row_level触发器 (要考虑优化 2005/2000各指出差别)
2.怎么实现触发器对于某些特殊语句不做处理 (提示:可以用批处理的上下文信息)
--sql server 2005
WITH test([name],Price,Number,Total)
AS (
SELECT *,x.Price * y.Number AS Total
FROM (
SELECT 'Fish' AS [name],3.5 AS Price UNION ALL
SELECT 'Meat' AS [name],6 AS Price UNION ALL
SELECT 'Shallot' AS [name],0.1 AS Price
)x
JOIN (
SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY a.[object_id])AS Number
FROM sys.objects a,sys.columns b
) y ON x.Price * y.Number < 100
)
SELECT x.*,y.*,z.*
FROM (SELECT * FROM test WHERE [name] = 'Fish')x
JOIN (SELECT * FROM test WHERE [name] = 'Meat')y
ON x.Total + y.Total < 100 AND x.Number + y.Number < 100
JOIN (SELECT * FROM test WHERE [name] = 'Shallot')z
ON x.Total + y.Total + z.Total = 100 AND x.Number + y.Number + z.Number = 100
AS (
SELECT *,x.Price * y.Number AS Total
FROM (
SELECT 'Fish' AS [name],3.5 AS Price UNION ALL
SELECT 'Meat' AS [name],6 AS Price UNION ALL
SELECT 'Shallot' AS [name],0.1 AS Price
)x
JOIN (
SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY a.[object_id])AS Number
FROM sys.objects a,sys.columns b
) y ON x.Price * y.Number < 100
)
SELECT x.*,y.*,z.*
FROM (SELECT * FROM test WHERE [name] = 'Fish')x
JOIN (SELECT * FROM test WHERE [name] = 'Meat')y
ON x.Total + y.Total < 100 AND x.Number + y.Number < 100
JOIN (SELECT * FROM test WHERE [name] = 'Shallot')z
ON x.Total + y.Total + z.Total = 100 AND x.Number + y.Number + z.Number = 100
这个问题大哥你也回答下嘛 我卡着个好几天了