mysql - 使用 SELECT 语句更新列

Question

我需要一个查询来更新选择(计数)语句中的列。

这是我尝试过的..

SELECT m.MaidID, m.MaidJobCompleted, count(m.MaidName) as countMaidJobCompleted
FROM  Booking b, Maid m 
WHERE b.BookingMaidID like concat("%",m.MaidName,"%") 
and b.BookingStatus="completed"
group by MaidName

获得正确结果:

MaidID  MaidJobCompleted    countMaidJobCompleted   
329        0                     5  
188        0                     1  
281        0                     8  
229        0                     4  

MaidJobCompleted 是我创建的列(默认值为 0)。countMaidJobCompleted 是从上面的查询中获取的列。

<小时/>

表格

女佣

MaidIDPrimary       int(100)     ,autoIncrement eg(1,2,3,4,5..)
MaidJobCompleted    int(100)     ,default value = 0
MaidName            varchar(200) ,eg(Batman,Spiderman,WonderWoman,Indiana John)

预订

BookingID           int(100)     ,autoIncrement eg(1,2,3,4,5..)
BookingMaidID       varchar(500) ,eg(Batman,Wonderwoman,Ironman)
BookingStatus       varchar(50)  ,eg(completed,pending..)

<小时/>

MaidID  MaidJobCompleted    countMaidJobCompleted   
329        0                     5  
188        0                     1  
281        0                     8  
229        0                     4  

<小时/>

现在，我想使用 UPDATE 查询根据“countMaidJobCompleted”更新“MaidJobCompleted”列。

Answer 1

首先，您应该规范化您的表。这意味着很多事情，但在您的上下文中，我看到在表 Maid 中，需要计算列 MaidJobCompleted 。这是一个禁忌。您最终可能会得到不同步的值(例如，新预订已完成，但计算值未更新)。但有一种方法可以确保这种情况不会发生。继续阅读...

另一个问题，BookingMaidID 应该是一个数字，它是 Maid 表中 MaidID 的外键。您将名称与 ID 混合在一起。

我给你的建议是:

女仆 table :

MaidID              int(100)     -- autoIncrement
MaidName            varchar(200) -- some name

预订表:

BookingID           int(100)     -- autoIncrement
MaidID              int(100)     -- foreign-key to Maid:MaidID     
StatusID            int(100)     -- foreign-key to Status:StatusID

状态表:

StatuID            int(100)      -- autoIncrement
StatusName         varchar(200)  -- some values, ex.completed,pending..

<小时/>

详细信息:

• 您不应该有需要计算的列值。需要时使用查询来做到这一点。
• 添加状态表是为了确保您能够控制预订表(和应用程序)中允许的有效状态值。

• 如果您需要知道所有女佣已完成多少预订，请使用以下命令:

  SELECT b.MaidID, m.MaidName, COUNT(b.BookingID)
  FROM Booking AS b
  JOIN Maid AS m ON b.MaidID = m.MaidID
  JOIN Status AS s ON b.StatusID = s.StatusID
  WHERE b.StatusID = (SELECT StatusID FROM Status WHERE StatusName = 'Completed')
  

• 或者，如果您需要了解 1 个特定女佣，请按如下方式修改:

  SELECT COUNT(b.BookingID)
  FROM Booking AS b
  JOIN Maid AS m ON b.MaidID = m.MaidID
  JOIN Status AS s ON b.StatusID = s.StatusID
  WHERE b.StatusID = (SELECT StatusID FROM Status WHERE StatusName = 'Completed')
    AND m.MaidName = 'THE-NAME-YOU-WANT'
  

让我们稍微解释一下该查询:

• 从预订表中，您可以计算女佣已完成的预订数量。
• 如果能显示女仆的名字就好了，所以加入 Maid 表来获取名字。
• 由于状态值位于其自己的表中，因此也加入状态表。

免责声明我直接在此处输入查询，没有在示例数据库上进行测试。