【SQL解惑】谜题11:工作顺序

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/jjjraddit/article/details/78698908
解惑一:
1、创建表并插入数据
create table Projects
(
workorder_id char(5) not null,
step_nbr integer not null check (step_nbr between 0 and 1000),
step_status char(1) not null
      check (step_status in ('C','W')),
      primary key (workorder_id,step_nbr)
)
insert into Projects(workorder_id,step_nbr,step_status)
values('AA100',0,'C'),
('AA100',1,'W'),
('AA100',2,'W'),
('AA200',0,'W'),
('AA200',1,'W'),
('AA300',0,'C'),
('AA300',1,'C')
2、实际上要找到的工序即是第0道工序为为完成Complete,其他非0工序为Waiting
select workorder_id
  from Projects as p1
 where step_nbr = 0
   and step_status = 'C'
   and 'W' = ALL(select step_status
                           from Projects as p2
                          where step_nbr <> 0
                            and p1.workorder_id = p2.workorder_id)
解惑二:
1、当第0道工序为Complete和非0工序为Waiting时则等于1,当工序的累加=Count函数时则证明满足
select workorder_id
from Projects
group by workorder_id
having SUM(case         
              when step_nbr <> 0 and step_status = 'W' then 1
              when step_nbr = 0 and step_status = 'C' then 1
              else 0 end) = COUNT(step_nbr)
解惑三:
1、方法一
select workorder_id
      from Projects
      group by workorder_id
      having COUNT(*) = COUNT(case when step_nbr = 0 and step_status = 'C'
                                          then 1
                                          else null end)
                              + COUNT(case when step_nbr <> 0 and step_status = 'W'
                                          then 1
                                          else null end )
2、方法二:利用了not null和check()约束
select workorder_id
from Projects
where step_status = 'C'
group by workorder_id
having SUM(step_nbr) = 0











展开阅读全文

sql parameters 解惑

02-24

private void button3_Click(object sender, EventArgs e)rn rn if (textBox1.Text == "")rn rn MessageBox.Show("请选选择查询要修改的记录");rn rn elsern rn SqlConnection con = new SqlConnection("server=(local);uid=sa;pwd=123;database=db_20");rn con.Open();rn SqlCommand com = new SqlCommand();rn com.Connection = con;rn com.CommandText = "fileUdate";rn com.CommandType = CommandType.StoredProcedure;rn com.Parameters.Add("@fileDate", SqlDbType.DateTime, 8);rn com.Parameters["@fileDate"].Value = dateTimePicker1.Value;rn com.Parameters.Add("@fileDay", SqlDbType.VarChar, 50);rn com.Parameters["@fileDay"].Value = comboBox1.SelectedIndex.ToString();rn com.Parameters.Add("@fileText", SqlDbType.VarChar, 2000);rn com.Parameters["@fileText"].Value = textBox1.Text;rn SqlParameter sqlpar = com.Parameters.Add("@returnfile", SqlDbType.VarChar, 1);rn sqlpar.Direction = ParameterDirection.Output;rn com.ExecuteNonQuery();rn [color=#FF0000]if (com.Parameters["@returnfile"].Value.ToString() == "2")rn[/color] rn MessageBox.Show("日记修改失败");rn rn if (com.Parameters["@returnfile"].Value.ToString() == "1")rn rn MessageBox.Show("日记修改成功");rn textBox1.Text = "";rn comboBox1.Text = "";rn rn if (com.Parameters["@returnfile"].Value.ToString() == "0")rn rn MessageBox.Show("没有此记录");rn rn rn rnrn在此,我在com.Parameters["@returnfile"].Value.ToString() == "2")有些疑问,不明白什么意思,为什么在这儿com.paramters["@returnfiles"].value.ToString() =="2",而不是“3”或是“1”rn高手解答下 论坛

oracle sql执行解惑

03-06

写了一个语句,详细如下:索引什么的都建了,大数据表salepayrpt_base 也用analyze 分析了,不带shopname like(and c.shopid in (select shopid from shop where shopname like '%凤%' ))条件执行时间约1秒不到,加上后执行时花了差不都有5分钟,第二次执行时也是只花了不到1秒钟,同一语句在三个数据库中执行都是同样的结构,个人认为可以排除数据库性能神马影响,请大牛解答!rnrnselect e.regionid,rn b.regionid as csregionid,rn b.regionname as cityname,rn a.shopid,rn c.shopname,rn sum(a.salevalue) as totalsalevalue,rn sum(a.discvalue) as totaldisvalue,rn g.salevalue - g.discvalue as saleamt,rn f.tsaleamt,rn g.custcount,rn decode(g.custcount,rn 0,rn 0,rn round((g.salevalue - g.discvalue) / g.custcount, 2)) as avgvaluern from salepayrpt_base a,rn region b,rn shop c,rn region e,rn (select a.shopid, sum(salevalue) as tsaleamtrn from salepayrpt_base a, region b, shop c, region ern where specialflag in (1, 2)rn and a.shopid = c.shopidrn and c.regionid = b.regionidrn and a.saletype not in (8, 9)rn and b.headregionid = e.regionidrn and trunc(a.sdate) >= to_date('2013-09-29', 'yyyy-mm-dd')rn and trunc(a.sdate) <= to_date('2014-03-05', 'yyyy-mm-dd')rn and c.shopid in (select shopid from shop where shopname like '%凤%' )rn and b.regionid = 401rn group by a.shopid) f,rn (select a.shopid,rn sum(salevalue) as salevalue,rn sum(discvalue) as discvalue,rn count(distinct a.sheetid) as custcountrn from salepayrpt_base a, region b, shop c, region ern where a.shopid = c.shopidrn and a.specialflag not in (1, 2)rn and a.saletype not in (8, 9)rn and c.regionid = b.regionidrn and b.headregionid = e.regionidrn and trunc(a.sdate) >= to_date('2013-09-29', 'yyyy-mm-dd')rn and trunc(a.sdate) <= to_date('2014-03-05', 'yyyy-mm-dd')rn and c.shopid in (select shopid from shop where shopname like '%凤%' )rn and b.regionid = 401rn group by a.shopid) grn where a.shopid = c.shopidrn and a.shopid = f.shopid(+)rn and a.shopid = g.shopid(+)rn and c.regionid = b.regionidrn and a.saletype not in (8, 9)rn and b.headregionid = e.regionidrn and trunc(a.sdate) >= to_date('2013-09-29', 'yyyy-mm-dd')rn and trunc(a.sdate) <= to_date('2014-03-05', 'yyyy-mm-dd')rn and a.shopid in (select shopid from shop where shopname like '%凤%' )rn rn and b.regionid = 401rn group by b. regionid,rn e.regionid,rn b.regionname,rn a.shopid,rn c.shopname,rn g.salevalue,rn g.discvalue,rn g.custcount,rn f.tsaleamtrn order by a.shopidrn 论坛

没有更多推荐了,返回首页