Использование SUM (Total). Оператор SQL отлично работает в PHP MySQL

#c# #database #visual-studio-2015

#c# #База данных #visual-studio-2015

Вопрос:

Я пытаюсь повторно использовать некоторый MySQL в моем проекте Visual Studio 2015 на C #. Я пытаюсь получить выходные данные из базы данных, которые суммируют продажи и общий объем продаж для каждого продавца. При использовании инструкции MySQL, которая отлично работает в моем PHP-проекте, я получаю сообщение об ошибке:

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)
);

Структура OFFICE:

 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. Сумма должна быть money , а не NCHAR . Суммирование в строке не имеет смысла. MySQL рад сделать все возможное, чтобы получить результат, а не выдавать ошибку. SQL Server этого не делает.

2. Я попытался изменить тип столбца на money , но я остался с тем же сообщением об ошибке.

3. Также дайте нам определения таблиц AGENT и OFFICE…

4. Вы пытаетесь сгруппировать по AGENT.AgentName , а тип Text . Попробуйте изменить его на nvarchar(100) . Sql Server не согласен группировать по Text типу данных.

5. Еще один, вы группируете по AGENT.AgentName , а затем пытаетесь выбрать OFFICE.OfficeLocation . Это также не будет работать в Sql Server.

Ответ №1:

Шаг 1

Измените свою таблицу агентов на

 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 не устраивает GROUP BY TEXT тип данных.

Шаг 2

Измените свой запрос на

 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

SQL Server также не рад выбирать столбец, который не содержится ни в агрегатной функции, ни в GROUP BY предложении при GROUP BY использовании.

Вы можете выполнить только шаг 2, не выполняя шаг 1, и это сработает, но Text тип данных устарел (ссылка на MSDN), поэтому вам не следует его использовать.