mysql查询用于从特定列中的 "1" "1-A"等中提取,"1-B"

Question

我有一个具有以下结构的表:

+---------+------------+| TableNo | ItemName   |+---------+------------+| 1       | X          || 2       | Y          || 1       | Z          || 1-A     | A          || 2-A     | B          |+---------+------------+

I am generating a report called SalesbyTableNo in which a user selects from a list the TableNos whose sales he wishes to see—but he can select only tables 1, 2, 3, etc: there is no option to select tables 1-A or 2-A (or 3-B, 4-F etc. which could also exist in the above shown table).

If the user selects table 1, I wish to show all items sold on table number "1" which includes 1, 1-A, 1-B, etc. I wish to get the output as below:

TableNo: 1  Sold ItemNames : X,Z,ATableNo: 2  Sold ItemNames : Y,B

The following is the query which I am currently using, which gives me reports of tables 1, 2, 3, etc. but does not include 1-A, 1-B, with included table 1:

strSQL = "select TableNo,Outlet,ItemName,id, Quantity, Value,
@i:= IF(Outlet = @last_outlet and TableNo = @last_tableno, @i + 1, 1) as result, 
@last_outlet := Outlet,@last_tableno := TableNo from (SELECT  @i := 0, 
@last_outlet := NULL, @last_tableno := NULL) h 
JOIN (SELECT Outlet, TableNo,ItemName, id, SUM(Quantity) AS Quantity,
SUM(Value) as Value From browsekot WHERE TableNo in " & list1_values
& " and Outlet = '" & rest.Text & "' and 
Date between '" & Format(DTPicker1.Value, "yyyy-mm-dd") 
& " ' And '" & Format(DTPicker2.Value, "yyyy-mm-dd") 
& "' GROUP BY ItemName,Outlet,TableNo ORDER BY Outlet,TableNo) i"

有没有办法通过修改上面的查询来做到这一点？

Answer 1

如果记录 1-A、1-B 等映射到整数 1、2、...，则相当不清楚为什么表的结构如此。 .. 无论如何。

您是否考虑过添加额外的列？

如果您打算这样做，则可以使用以下命令从TableNo中提取正确的值:

SELECT CAST(TableNo as UNSIGNED)

正如 Eggyal 在评论中提到的，这会将 TableNo 转换为无符号整数，直到遇到的第一个非数字字符。

当然，您必须将其调整到您的代码中，但这是将文本转换为可用格式的基本方法。