sql - 有可能在sql中做到这一点吗？

我正在点餐系统食物

我要这样做，当所有菜式都为“完成”时，订单自动更改为完成

我有4张桌子

1个订单

ID_ORDER:OR00001

状态:处理中/更改自动“完成”

CREATION_DATE:2017年7月17日

3 DISH

1个DISH
PASTA

编号:PA00001

姓名:PASTA MIDELA

主厨:杰夫

ID_ORDER:OR00001

状态:处理中

2 DISH SALAD

编号:SA00001

姓名:饮食沙拉

首席:阿斯顿

ID_ORDER:OR00001

状态:处理中

3 DISH MEAT

编号:ME00001

名称:肉BBF

主厨:杰夫

ID_ORDER:OR00001

状态:处理中

怎么可能？ 谢谢

CREATE TABLE [dbo].[ORDER](
    [ID_OR] [int] IDENTITY(1,1) NOT NULL,
    [ID_ORDER]  AS ('OR'+right('00000'+CONVERT([varchar],[ID_OR],(0)),(5))),
    [STATUS] [datetime] NOT NULL,
    [CREATION_DATE] [datetime] NOT NULL,
 CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED
(
    [ID_ORDER] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

CREATE TABLE [dbo].[1_DISH](
    [ID_D1] [int] IDENTITY(1,1) NOT NULL,
    [ID_PASTA]  AS ('PA'+right('00000'+CONVERT([varchar],[ID_D1],(0)),(5))),
    [NAME] [varchar](20),
    [CHEF] [varchar](20),
    [STATUS] [datetime] NOT NULL,
    [ID_ORDER] [varchar](20)
 CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED
(
    [ID_PASTA] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

CREATE TABLE [dbo].[2_DISH](
    [ID_D2] [int] IDENTITY(1,1) NOT NULL,
    [ID_SALAD]  AS ('SA'+right('00000'+CONVERT([varchar],[ID_D2],(0)),(5))),
    [NAME] [varchar](20),
    [CHEF] [varchar](20),
    [STATUS] [datetime] NOT NULL,
    [ID_ORDER] [varchar](20)
 CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED
(
    [ID_SALAD] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

CREATE TABLE [dbo].[3_DISH](
    [ID_D3] [int] IDENTITY(1,1) NOT NULL,
    [ID_MEAT]  AS ('ME'+right('00000'+CONVERT([varchar],[ID_D3],(0)),(5))),
    [NAME] [varchar](20),
    [CHEF] [varchar](20),
    [STATUS] [datetime] NOT NULL,
    [ID_ORDER] [varchar](20)
 CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED
(
    [ID_MEAT] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

最佳答案

以下示例代码可能会为您提供帮助。

创建两个表:一个表用于订单，第二个表用于包含订单的菜肴。

我在Dish表中添加了 DishType 列。
我将状态更改为varchar数据类型。

declare @ORDERTABLE table
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [STATUS] [varchar](20) NOT NULL,
    [CREATION_DATE] [datetime] NOT NULL
)

declare @DISHTABLE table
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DISHTYPE] varchar(20),
    [NAME] [varchar](20),
    [CHEF] [varchar](20),
    [STATUS] [varchar](20) NOT NULL,
    [ID_ORDER] int
)

插入了一些样本数据。

insert @ORDERTABLE (STATUS, CREATION_DATE) values
('IN PROCESS', getdate()),
('IN PROCESS', getdate());

insert @DISHTABLE (DISHTYPE, NAME, CHEF, STATUS, ID_ORDER) values
('PASTA','PASTA MIDELA','JEFF','IN PROCESS',1),
('SALAD','DIET SALAD','KIA','IN PROCESS',1),
('MEAT','MEAT BBF','BART','IN PROCESS',1),
('SALAD','CAESAR SALAD','KIA','IN PROCESS',2),
('MEAT','ROAST CHICKEN','BART','IN PROCESS',2);

update @DISHTABLE set STATUS = 'COMPLETE' where ID = 1;
update @DISHTABLE set STATUS = 'COMPLETE' where ID = 2;
update @DISHTABLE set STATUS = 'COMPLETE' where ID = 3;

select * from @DISHTABLE;

一种自动更改“订单”状态的简单方法:每当更改某个菜式的状态时，请检查订单中有多少菜与完成了多少菜。如果所有菜肴均已完成，则将订单状态也更改为已完成。

declare @ORDERID int = 1;

update @ORDERTABLE
    set STATUS = 'COMPLETE'
where
    ID = @ORDERID
    and (select count(*) from @DISHTABLE where ID_ORDER = @ORDERID) = (select count(*) from @DISHTABLE where ID_ORDER = @ORDERID and STATUS = 'COMPLETE');


select
    o.ID,
    DISHES = (select count(*) from @DISHTABLE where ID_ORDER = o.ID),
    COMPLETED = (select count(*) from @DISHTABLE where ID_ORDER = o.ID and STATUS = 'COMPLETE'),
    o.STATUS
from @ORDERTABLE o;

关于sql - 有可能在sql中做到这一点吗？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45071023/