我试图在我的Visual Studio 2015 c#项目中重用一些mySQL。我试图从数据库中检索输出,其中总结了每个销售人员的销售和总销售量。当在我的PHP项目中使用mySQL语句时,我得到了错误:
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
代理结构:
CREATE TABLE [dbo].[AGENT] (
[AgentID] INT IDENTITY (1, 1) NOT NULL,
[AgentName] TEXT NOT NULL,
[OfficeKey] INT NOT NULL,
PRIMARY KEY CLUSTERED ([AgentID] ASC)
);
办公室结构:
CREATE TABLE [dbo].[OFFICE] (
[OfficeID] INT IDENTITY (1, 1) NOT NULL,
[OfficeLocation] NCHAR (20) NOT NULL,
PRIMARY KEY CLUSTERED ([OfficeID] ASC)
);
销售结构:
CREATE TABLE [dbo].[SALE] (
[SaleID] INT IDENTITY (1, 1) NOT NULL,
[SaleDate] DATE NOT NULL,
[AgentKey] INT NOT NULL,
[Amount] MONEY NOT NULL,
[DestinationKey] INT NOT NULL,
PRIMARY KEY CLUSTERED ([SaleID] ASC)
);
来源:
namespace Desktop_Campus_Travel
{
public partial class Agent_Bookings : Form
{
public Agent_Bookings()
{
InitializeComponent();
}
private void Agent_Bookings_Load(object sender, EventArgs e)
{
List<AgentBooking> agentList = new List<AgentBooking>();
SqlConnection conn = Database.GetConnection();
string selStmt = @"
SELECT AGENT.AgentName,
OFFICE.OfficeLocation,
COUNT(AGENT.AgentID) AS Sales,
SUM(SALE.Amount) AS Total
FROM AGENT
JOIN OFFICE
ON AGENT.OfficeKey = OFFICE.OfficeID
JOIN SALE
ON SALE.AgentKey = AGENT.AgentID
GROUP BY AGENT.AgentName";
SqlCommand selCmd = new SqlCommand(selStmt, conn);
try
{
conn.Open();
SqlDataReader reader = selCmd.ExecuteReader();
while (reader.Read())
{
AgentBooking agent = new AgentBooking();
agent.AgentName = reader["AgentName"].ToString();
agent.OfficeLocation = reader["OfficeLocation"].ToString();
agent.Sales = reader["Sales"].ToString();
agent.Total = reader["Total"].ToString();
agentList.Add(agent);
}
reader.Close();
}
catch (SqlException ex) { throw ex; }
finally { conn.Close(); }
}
private void button1_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
步骤1
将Agent Table更改为
CREATE TABLE [dbo].[AGENT] (
[AgentID] INT IDENTITY (1, 1) NOT NULL,
[AgentName] NVARCHAR(100) NOT NULL,
[OfficeKey] INT NOT NULL,
PRIMARY KEY CLUSTERED ([AgentID] ASC)
);
SQL Server not happy to GROUP BY
to TEXT
data type.
将查询更改为
SELECT
AGENT.AgentName,
OFFICE.OfficeLocation,
SALE2.Sales,
SALE2.Total
FROM
AGENT
JOIN OFFICE ON AGENT.OfficeKey = OFFICE.OfficeID
JOIN
(
SELECT
SALE.AgentKey,
COUNT(SALE.SaleID) AS Sales,
SUM(SALE.Amount) AS Total
FROM SALE
GROUP BY SALE.AgentKey
) SALE2 ON SALE2.AgentKey = AGENT.AgentID
当使用GROUP BY
时,SQL Server也不乐意选择不包含在聚合函数或GROUP BY
子句中的列。
你可以只做第2步而不做第1步,它将工作,但Text
数据类型已弃用(MSDN参考),所以你不应该使用它。