神奇的sql

夏木

/食品表一            string strQures1 = "select ROW_NUMBER() OVER (ORDER BY Enterprise.UnitName DESC) ,Enterprise.UnitName,Enterprise.Province ,Enterprise.City ,Enterprise.County ,CAST(Tab_food.A_1 as bigint),CAST(Tab_food.A_1 as bigint),CAST(Tab_food.A_1 as bigint),CAST(Tab_food.A_2 as bigint),CAST(Tab_food.A_2 as bigint),CAST(Tab_food.A_2 as bigint),CAST(Tab_food.A_3 as bigint),CAST(Tab_food.A_3 as bigint),CAST(Tab_food.A_3 as bigint),CAST(Tab_food.A_4 as bigint),CAST(Tab_food.A_4 as bigint),CAST(Tab_food.A_4 as bigint),CAST(Tab_food.A_5 as bigint),CAST(Tab_food.A_5 as bigint),CAST(Tab_food.A_5 as bigint),CAST(Tab_food.A_6 as bigint),CAST(Tab_food.A_6 as bigint),CAST(Tab_food.A_6 as bigint),CAST(Tab_food.A_7 as bigint),CAST(Tab_food.A_7 as bigint),CAST(Tab_food.A_7 as bigint),CAST(Tab_food.A_8 as bigint),CAST(Tab_food.A_8 as bigint),CAST(Tab_food.A_8 as bigint),CAST(Tab_food.A_9 as bigint),CAST(Tab_food.A_9 as bigint),CAST(Tab_food.A_9 as bigint),CAST(Tab_food.A_10 as bigint),CAST(Tab_food.A_10 as bigint),CAST(Tab_food.A_10 as bigint),CAST(Tab_food.A_11 as bigint),CAST(Tab_food.A_11 as bigint),CAST(Tab_food.A_11 as bigint),CAST(Tab_food.B_1 as bigint),CAST(Tab_food.B_1 as bigint),CAST(Tab_food.B_1 as bigint),CAST(Tab_food.B_2 as bigint),CAST(Tab_food.B_2 as bigint),CAST(Tab_food.B_2 as bigint),CAST(Tab_food.B_3 as bigint),CAST(Tab_food.B_3 as bigint),CAST(Tab_food.B_3 as bigint),CAST(Tab_food.B_4 as bigint),CAST(Tab_food.B_4 as bigint),CAST(Tab_food.B_4 as bigint),CAST(Tab_food.B_5 as bigint),CAST(Tab_food.B_5 as bigint),CAST(Tab_food.B_5 as bigint),CAST(Tab_food.B_6 as bigint),CAST(Tab_food.B_6 as bigint),CAST(Tab_food.B_6 as bigint),CAST(Tab_food.B_7 as bigint),CAST(Tab_food.B_7 as bigint),CAST(Tab_food.B_7 as bigint),CAST(Tab_food.B_8 as bigint),CAST(Tab_food.B_8 as bigint),CAST(Tab_food.B_8 as bigint),CAST(Tab_food.B_9 as bigint),CAST(Tab_food.B_9 as bigint),CAST(Tab_food.B_9 as bigint),CAST(Tab_food.B_10 as bigint),CAST(Tab_food.B_10 as bigint),CAST(Tab_food.B_10 as bigint),CAST(Tab_food.B_11 as bigint),CAST(Tab_food.B_11 as bigint),CAST(Tab_food.B_11 as bigint),CAST(Tab_food.C_1 as bigint),CAST(Tab_food.C_1 as bigint),CAST(Tab_food.C_1 as bigint),CAST(Tab_food.C_2 as bigint),CAST(Tab_food.C_2 as bigint),CAST(Tab_food.C_2 as bigint),CAST(Tab_food.C_3 as bigint),CAST(Tab_food.C_3 as bigint),CAST(Tab_food.C_3 as bigint),CAST(Tab_food.C_4 as bigint),CAST(Tab_food.C_4 as bigint),CAST(Tab_food.C_4 as bigint),CAST(Tab_food.C_5 as bigint),CAST(Tab_food.C_5 as bigint),CAST(Tab_food.C_5 as bigint),CAST(Tab_food.C_6 as bigint),CAST(Tab_food.C_6 as bigint),CAST(Tab_food.C_6 as bigint),CAST(Tab_food.C_7 as bigint),CAST(Tab_food.C_7 as bigint),CAST(Tab_food.C_7 as bigint),CAST(Tab_food.C_8 as bigint),CAST(Tab_food.C_8 as bigint),CAST(Tab_food.C_8 as bigint),CAST(Tab_food.C_9 as bigint),CAST(Tab_food.C_9 as bigint),CAST(Tab_food.C_9 as bigint),CAST(Tab_food.C_10 as bigint),CAST(Tab_food.C_10 as bigint),CAST(Tab_food.C_10 as bigint),CAST(Tab_food.C_11 as bigint),CAST(Tab_food.C_11 as bigint),CAST(Tab_food.C_11 as bigint),CAST(Tab_food.D_1 as bigint),CAST(Tab_food.D_1 as bigint),CAST(Tab_food.D_1 as bigint),CAST(Tab_food.D_2 as bigint),CAST(Tab_food.D_2 as bigint),CAST(Tab_food.D_2 as bigint),CAST(Tab_food.D_3 as bigint),CAST(Tab_food.D_3 as bigint),CAST(Tab_food.D_3 as bigint),CAST(Tab_food.D_4 as bigint),CAST(Tab_food.D_4 as bigint),CAST(Tab_food.D_4 as bigint),CAST(Tab_food.D_5 as bigint),CAST(Tab_food.D_5 as bigint),CAST(Tab_food.D_5 as bigint),CAST(Tab_food.D_6 as bigint),CAST(Tab_food.D_6 as bigint),CAST(Tab_food.D_6 as bigint),CAST(Tab_food.D_7 as bigint),CAST(Tab_food.D_7 as bigint),CAST(Tab_food.D_7 as bigint),CAST(Tab_food.D_8 as bigint),CAST(Tab_food.D_8 as bigint),CAST(Tab_food.D_8 as bigint),CAST(Tab_food.D_9 as bigint),CAST(Tab_food.D_9 as bigint),CAST(Tab_food.D_9 as bigint),CAST(Tab_food.D_10 as bigint),CAST(Tab_food.D_10 as bigint),CAST(Tab_food.D_10 as bigint),CAST(Tab_food.D_11 as bigint),CAST(Tab_food.D_11 as bigint),CAST(Tab_food.D_11 as bigint),CAST(Tab_food.E_1 as bigint),CAST(Tab_food.E_1 as bigint),CAST(Tab_food.E_1 as bigint),CAST(Tab_food.E_2 as bigint),CAST(Tab_food.E_2 as bigint),CAST(Tab_food.E_2 as bigint),CAST(Tab_food.E_3 as bigint),CAST(Tab_food.E_3 as bigint),CAST(Tab_food.E_3 as bigint),CAST(Tab_food.E_4 as bigint),CAST(Tab_food.E_4 as bigint),CAST(Tab_food.E_4 as bigint),CAST(Tab_food.E_5 as bigint),CAST(Tab_food.E_5 as bigint),CAST(Tab_food.E_5 as bigint),CAST(Tab_food.E_6 as bigint),CAST(Tab_food.E_6 as bigint),CAST(Tab_food.E_6 as bigint),CAST(Tab_food.E_7 as bigint),CAST(Tab_food.E_7 as bigint),CAST(Tab_food.E_7 as bigint),CAST(Tab_food.E_8 as bigint),CAST(Tab_food.E_8 as bigint),CAST(Tab_food.E_8 as bigint),CAST(Tab_food.E_9 as bigint),CAST(Tab_food.E_9 as bigint),CAST(Tab_food.E_9 as bigint),CAST(Tab_food.E_10 as bigint),CAST(Tab_food.E_10 as bigint),CAST(Tab_food.E_10 as bigint),CAST(Tab_food.E_11 as bigint),CAST(Tab_food.E_11 as bigint),CAST(Tab_food.E_11 as bigint),CAST(Tab_food.F_1 as bigint),CAST(Tab_food.F_1 as bigint),CAST(Tab_food.F_1 as bigint),CAST(Tab_food.F_2 as bigint),CAST(Tab_food.F_2 as bigint),CAST(Tab_food.F_2 as bigint),CAST(Tab_food.F_3 as bigint),CAST(Tab_food.F_3 as bigint),CAST(Tab_food.F_3 as bigint),CAST(Tab_food.F_4 as bigint),CAST(Tab_food.F_4 as bigint),CAST(Tab_food.F_4 as bigint),CAST(Tab_food.F_5 as bigint),CAST(Tab_food.F_5 as bigint),CAST(Tab_food.F_5 as bigint),CAST(Tab_food.F_6 as bigint),CAST(Tab_food.F_6 as bigint),CAST(Tab_food.F_6 as bigint),CAST(Tab_food.F_7 as bigint),CAST(Tab_food.F_7 as bigint),CAST(Tab_food.F_7 as bigint),CAST(Tab_food.F_8 as bigint),CAST(Tab_food.F_8 as bigint),CAST(Tab_food.F_8 as bigint),CAST(Tab_food.F_9 as bigint),CAST(Tab_food.F_9 as bigint),CAST(Tab_food.F_9 as bigint),CAST(Tab_food.F_10 as bigint),CAST(Tab_food.F_10 as bigint),CAST(Tab_food.F_10 as bigint),CAST(Tab_food.F_11 as bigint),CAST(Tab_food.F_11 as bigint),CAST(Tab_food.F_11 as bigint),CAST(Tab_food.G_1 as bigint),CAST(Tab_food.G_1 as bigint),CAST(Tab_food.G_1 as bigint),CAST(Tab_food.G_2 as bigint),CAST(Tab_food.G_2 as bigint),CAST(Tab_food.G_2 as bigint),CAST(Tab_food.G_3 as bigint),CAST(Tab_food.G_3 as bigint),CAST(Tab_food.G_3 as bigint),CAST(Tab_food.G_4 as bigint),CAST(Tab_food.G_4 as bigint),CAST(Tab_food.G_4 as bigint),CAST(Tab_food.G_5 as bigint),CAST(Tab_food.G_5 as bigint),CAST(Tab_food.G_5 as bigint),CAST(Tab_food.G_6 as bigint),CAST(Tab_food.G_6 as bigint),CAST(Tab_food.G_6 as bigint),CAST(Tab_food.G_7 as bigint),CAST(Tab_food.G_7 as bigint),CAST(Tab_food.G_7 as bigint),CAST(Tab_food.G_8 as bigint),CAST(Tab_food.G_8 as bigint),CAST(Tab_food.G_8 as bigint),CAST(Tab_food.G_9 as bigint),CAST(Tab_food.G_9 as bigint),CAST(Tab_food.G_9 as bigint),CAST(Tab_food.G_10 as bigint),CAST(Tab_food.G_10 as bigint),CAST(Tab_food.G_10 as bigint),CAST(Tab_food.G_11 as bigint),CAST(Tab_food.G_11 as bigint),CAST(Tab_food.G_11 as bigint),CAST(Tab_food.H_1 as bigint),CAST(Tab_food.H_1 as bigint),CAST(Tab_food.H_1 as bigint),CAST(Tab_food.I_1 as bigint),CAST(Tab_food.I_1 as bigint),CAST(Tab_food.I_1 as bigint),CAST(Tab_food.J_1 as bigint),CAST(Tab_food.J_1 as bigint),CAST(Tab_food.J_1 as bigint),CAST(Enterprise.TotalPeople as bigint),CAST(T20.C as float) ,CAST(T20.D as float) ,CAST(T20.E as float),CAST(T20.K  as float),CAST(T20.F as float),CAST(T20.G as float),CAST(T20.H as float) from Enterprise inner join  T27 on Enterprise.code =T27.EnterpriseCode inner join Tab_food on T27.id =Tab_food.T27ID inner join  T20 on T20.T27ID = Tab_food.T27ID";            

夏木

//食品表二
            string strQures3 = "select ROW_NUMBER() OVER (ORDER BY Enterprise.UnitName DESC) ,CAST((case when CAST(Tab_food.E_1 as bigint)=0 then 0 else CAST(Tab_food.G_1 AS float)*100/CAST(Tab_food.E_1 AS float) end) as decimal(18,2)),CAST((case when CAST(Tab_food.E_2 as bigint)=0 then 0 else CAST(Tab_food.G_2 AS float)*100/CAST(Tab_food.E_2 AS float) end) as decimal(18,2)),CAST((case when CAST(Tab_food.E_3 as bigint)=0 then 0 else CAST(Tab_food.G_3 AS float)*100/CAST(Tab_food.E_3 AS float) end) as decimal(18,2)),CAST((case when CAST(Tab_food.E_4 as bigint)=0 then 0 else CAST(Tab_food.G_4 AS float)*100/CAST(Tab_food.E_4 AS float) end) as decimal(18,2)),CAST((case when CAST(Tab_food.E_5 as bigint)=0 then 0 else CAST(Tab_food.G_5 AS float)*100/CAST(Tab_food.E_5 AS float) end) as decimal(18,2)),CAST((case when CAST(Tab_food.E_6 as bigint)=0 then 0 else CAST(Tab_food.G_6 AS float)*100/CAST(Tab_food.E_6 AS float) end) as decimal(18,2)),CAST((case when CAST(Tab_food.E_7 as bigint)=0 then 0 else CAST(Tab_food.G_7 AS float)*100/CAST(Tab_food.E_7 AS float) end) as decimal(18,2)),CAST((case when CAST(Tab_food.E_8 as bigint)=0 then 0 else CAST(Tab_food.G_8 AS float)*100/CAST(Tab_food.E_8 AS float) end) as decimal(18,2))  ,CAST((case when CAST(Tab_food.E_9 as bigint)=0 then 0 else CAST(Tab_food.G_9 AS float)*100/CAST(Tab_food.E_9 AS float) end) as decimal(18,2)),CAST((case when CAST(Tab_food.E_10 as bigint)=0 then 0 else CAST(Tab_food.G_10 AS float)*100/CAST(Tab_food.E_10 AS float) end) as decimal(18,2)),CAST((case when CAST(Tab_food.E_11 as bigint)=0 then 0 else CAST(Tab_food.G_11 AS float)*100/CAST(Tab_food.E_11 AS float) end) as decimal(18,2)),CAST((case when CAST(Tab_food.B_1 as bigint)=0 then 0 else CAST(Tab_food.E_1 AS float)*1000/CAST(Tab_food.B_1 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_2 as bigint)=0 then 0 else CAST(Tab_food.E_2 AS float)*1000/CAST(Tab_food.B_2 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_3 as bigint)=0 then 0 else CAST(Tab_food.E_3 AS float)*1000/CAST(Tab_food.B_3 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_4 as bigint)=0 then 0 else CAST(Tab_food.E_4 AS float)*1000/CAST(Tab_food.B_4 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_5 as bigint)=0 then 0 else CAST(Tab_food.E_5 AS float)*1000/CAST(Tab_food.B_5 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_6 as bigint)=0 then 0 else CAST(Tab_food.E_6 AS float)*1000/CAST(Tab_food.B_6 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_7 as bigint)=0 then 0 else CAST(Tab_food.E_7 AS float)*1000/CAST(Tab_food.B_7 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_8 as bigint)=0 then 0 else CAST(Tab_food.E_8 AS float)*1000/CAST(Tab_food.B_8 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_9 as bigint)=0 then 0 else CAST(Tab_food.E_9 AS float)*1000/CAST(Tab_food.B_9 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_10 as bigint)=0 then 0 else CAST(Tab_food.E_10 AS float)*1000/CAST(Tab_food.B_10 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_11 as bigint)=0 then 0 else CAST(Tab_food.E_11 AS float)*1000/CAST(Tab_food.B_11 AS float) end)as decimal(18,2)) ,CAST((case when CAST(Tab_food.B_1 as bigint)=0 then 0 else CAST(Tab_food.F_1 AS float)*1000/CAST(Tab_food.B_1 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_2 as bigint)=0 then 0 else CAST(Tab_food.F_2 AS float)*1000/CAST(Tab_food.B_2 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_3 as bigint)=0 then 0 else CAST(Tab_food.F_3 AS float)*1000/CAST(Tab_food.B_3 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_4 as bigint)=0 then 0 else CAST(Tab_food.F_4 AS float)*1000/CAST(Tab_food.B_4 AS float) end)as decimal(18,2)) ,CAST((case when CAST(Tab_food.B_5 as bigint)=0 then 0 else CAST(Tab_food.F_5 AS float)*1000/CAST(Tab_food.B_5 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_6 as bigint)=0 then 0 else CAST(Tab_food.F_6 AS float)*1000/CAST(Tab_food.B_6 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_7 as bigint)=0 then 0 else CAST(Tab_food.F_7 AS float)*1000/CAST(Tab_food.B_7 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_8 as bigint)=0 then 0 else CAST(Tab_food.F_8 AS float)*1000/CAST(Tab_food.B_8 AS float) end)as decimal(18,2)) ,CAST((case when CAST(Tab_food.B_9 as bigint)=0 then 0 else CAST(Tab_food.F_9 AS float)*1000/CAST(Tab_food.B_9 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_10 as bigint)=0 then 0 else CAST(Tab_food.F_10 AS float)*1000/CAST(Tab_food.B_10 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.B_11 as bigint)=0 then 0 else CAST(Tab_food.F_11 AS float)*1000/CAST(Tab_food.B_11 AS float) end)as decimal(18,2)) ,CAST((case when CAST(Tab_food.C_1 as bigint)=0 then 0 else CAST(Tab_food.B_1 AS float)*100/CAST(Tab_food.C_1 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.C_2 as bigint)=0 then 0 else CAST(Tab_food.B_2 AS float)*100/CAST(Tab_food.C_2 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.C_3 as bigint)=0 then 0 else CAST(Tab_food.B_3 AS float)*100/CAST(Tab_food.C_3 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.C_4 as bigint)=0 then 0 else CAST(Tab_food.B_4 AS float)*100/CAST(Tab_food.C_4 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.C_5 as bigint)=0 then 0 else CAST(Tab_food.B_5 AS float)*100/CAST(Tab_food.C_5 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.C_6 as bigint)=0 then 0 else CAST(Tab_food.B_6 AS float)*100/CAST(Tab_food.C_6 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.C_7 as bigint)=0 then 0 else CAST(Tab_food.B_7 AS float)*100/CAST(Tab_food.C_7 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.C_8 as bigint)=0 then 0 else CAST(Tab_food.B_8 AS float)*100/CAST(Tab_food.C_8 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.C_9 as bigint)=0 then 0 else CAST(Tab_food.B_9 AS float)*100/CAST(Tab_food.C_9 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.C_10 as bigint)=0 then 0 else CAST(Tab_food.B_10 AS float)*100/CAST(Tab_food.C_10 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.C_11 as bigint)=0 then 0 else CAST(Tab_food.B_11 AS float)*100/CAST(Tab_food.C_11 AS float) end)as decimal(18,2)) ,CAST((case when CAST(Tab_food.A_1 as bigint)=0 then 0 else CAST(Tab_food.C_1 AS float)*100/CAST(Tab_food.A_1 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.A_2 as bigint)=0 then 0 else CAST(Tab_food.C_2 AS float)*100/CAST(Tab_food.A_2 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.A_3 as bigint)=0 then 0 else CAST(Tab_food.C_3 AS float)*100/CAST(Tab_food.A_3 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.A_4 as bigint)=0 then 0 else CAST(Tab_food.C_4 AS float)*100/CAST(Tab_food.A_4 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.A_5 as bigint)=0 then 0 else CAST(Tab_food.C_5 AS float)*100/CAST(Tab_food.A_5 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.A_6 as bigint)=0 then 0 else CAST(Tab_food.C_6 AS float)*100/CAST(Tab_food.A_6 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.A_7 as bigint)=0 then 0 else CAST(Tab_food.C_7 AS float)*100/CAST(Tab_food.A_7 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.A_8 as bigint)=0 then 0 else CAST(Tab_food.C_8 AS float)*100/CAST(Tab_food.A_8 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.A_9 as bigint)=0 then 0 else CAST(Tab_food.C_9 AS float)*100/CAST(Tab_food.A_9 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.A_10 as bigint)=0 then 0 else CAST(Tab_food.C_10 AS float)*100/CAST(Tab_food.A_10 AS float) end)as decimal(18,2)),CAST((case when CAST(Tab_food.A_11 as bigint)=0 then 0 else CAST(Tab_food.C_11 AS float)*100/CAST(Tab_food.A_11 AS float) end)as decimal(18,2)) ,T27.ForcedCommit from Enterprise inner join  T27 on Enterprise.code =T27.EnterpriseCode inner join Tab_food on T27.id =Tab_food.T27ID inner join  T20 on T20.T27ID = Tab_food.T27ID";
            

夏木

看到我同事写这样的sql。。我还要表达啥。。任何言语已经无法表达我此刻的心情。。。。

木魚

这个时候不是一般应该说心中千万只草泥马狂奔而过吗……
这么长的sql能写出来也不容易啊……为啥这么偏爱CAST……

夏木

木魚 发表于 2013-10-25 11:21
这个时候不是一般应该说心中千万只草泥马狂奔而过吗……
这么长的sql能写出来也不容易啊……为啥 ...

我一般不说这句话的。。我也觉得他太不容易了。。哎。。我看着都头疼。。他耐心真好。。不过我觉得应该有好办法可以解决这么长的sql吧。。纯手工太麻烦了。。

樱子

夏木

樱子 发表于 2013-10-25 13:08

小默

shenlan

呵呵,,,````````````````

Basshunter

这个执行效率不要了吗？