mysql - Coalesce Mysql Datatable服务器端

我想在rincian_order表中链接的mainproduk上显示所有产品数据，但是如果rincian_order表中没有链接的产品数据，我想通过将'omzet'填充为0来继续显示它，则输入代码“ coalesce（ sum（rincian_order.jumlah_pc）/ 6），0）作为omzet“”作为营业额，但是这样的代码是错误的，有人可以帮忙吗？


  错误号：1054“字段列表”中的未知列“ 0）”

    $this->datatables->select("mainproduk.id as id_m,mainproduk.barcode as barcod,mainproduk.nomor_kemtan as nomor_kemtan,mainproduk.nama_produk as nama_produk,mainproduk.satuan as satuan,mainproduk.nama_alias as nama_alias,mainproduk.produk_jadi as produk_jadi,mainproduk.nama_alias as nama_alias,mainproduk.nama_alias as nama_alias,mainproduk.nama_alias as nama_alias,mainproduk.min_stok_kemasan as min_stok_kemasan,mainproduk.tipe_produk as tipe_produk,mainproduk.top_item as top_item,mainproduk.status as status, coalesce(sum(rincian_order.jumlah_pc)/6), 0) as omzet");
    $this->datatables->from("mainproduk");
    $this->datatables->join("rincian_order", "mainproduk.barcode = rincian_order.barcode", "left");
    $this->datatables->where("mainproduk.status =", "1");
    $this->datatables->where("rincian_order.tipe =", "po");
    $this->datatables->where("rincian_order.status !=", "canceled");
    $this->datatables->where("rincian_order.tanggal_kirim >=", "2017-11-01");
    $this->datatables->where("rincian_order.tanggal_kirim <=", "2018-04-30");
    $this->datatables->group_by("mainproduk.id");
    $this->db->order_by("mainproduk.id", "ASC");
    $this->datatables->add_column("view", "<a href='editproduk/$1'><span class='glyphicon glyphicon-edit' aria-hidden='true'></span></a> | <a href='logproduk/$1'><span class='fa fa-fw fa-history'></span></a>", "id_m");

    return $this->datatables->generate();

我设法使用过下面的代码，但是在数据表服务器端不起作用

SELECT mainproduk.id as id,
    mainproduk.barcode as barcod,
    mainproduk.nama_produk,
    mainproduk.nama_alias,
    mainproduk.satuan,
    mainproduk.produk_jadi,
    mainproduk.kemasan,
    mainproduk.min_stok_kemasan,
    mainproduk.status,
    mainproduk.top_item,
    mainproduk.tipe_produk,
    mainproduk.nomor_kemtan,
    coalesce(sum(R.jumlah_pc),0) as omzet
    FROM mainproduk
    LEFT JOIN
    (
        SELECT id,barcode, jumlah_pc
        FROM rincian_order WHERE tipe='po' AND status!='canceled' AND tanggal_kirim BETWEEN '2017-11-01' AND '2018-04-30'
    ) AS R
        ON mainproduk.barcode = R.barcode WHERE status=1 GROUP BY mainproduk.id ORDER BY mainproduk.id ASC

我的控制器

function json() {
    header('Content-Type: application/json');
    echo $this->ceklisqc_model->json();
}

我的脚本数据表服务器端

    $(function () {

    $.fn.dataTableExt.oApi.fnPagingInfo = function(oSettings)
    {
      return {
        "iStart": oSettings._iDisplayStart,
        "iEnd": oSettings.fnDisplayEnd(),
        "iLength": oSettings._iDisplayLength,
        "iTotal": oSettings.fnRecordsTotal(),
        "iFilteredTotal": oSettings.fnRecordsDisplay(),
        "iPage": Math.ceil(oSettings._iDisplayStart / oSettings._iDisplayLength),
        "iTotalPages": Math.ceil(oSettings.fnRecordsDisplay() / oSettings._iDisplayLength)
      };
    };

    var t = $("#example1").dataTable({
      initComplete: function() {
        var api = this.api();
        $('#example1_filter input')
        .off('.DT')
        .on('keyup.DT', function(e) {
          if (e.keyCode == 13) {
            api.search(this.value).draw();
          }
        });
      },
      oLanguage: {
        sProcessing: "loading..."
      },
      processing: true,
      serverSide: true,
      ajax: {"url": "json", "type": "POST"},
      columns: [
        {
          "data": "id_m",
          "orderable": false
        },
        {"data": "barcod"},
        {"data": "nomor_kemtan"},
        {"data": "nama_produk"},
        {"data": "satuan"},
        {"data": "nama_alias"},
        {"data": "produk_jadi"},
        {"data": "omzet"},
        {"data": "nama_alias"},
        {"data": "nama_alias"},
        {"data": "min_stok_kemasan"},
        {"data": "tipe_produk"},
        {"data": "top_item"},
        {"data": "status"},
        {"data": "view"}
      ],
      order: [[1, 'asc']],
        rowCallback: function(row, data, iDisplayIndex) {
          var info = this.fnPagingInfo();
          var page = info.iPage;
          var length = info.iLength;
          var index = page * length + (iDisplayIndex + 1);
          $('td:eq(0)', row).html(index);
        }
    });
});

最佳答案

我认为您的select语句中只有一个小的错字。更改此：

coalesce(sum(rincian_order.jumlah_pc)/6), 0) as omzet

对此：

coalesce(sum(rincian_order.jumlah_pc) / 6, 0) as omzet

我最初评论说，因为您要按mainproduk.id分组，但选择了许多非聚合列，因此查询无效。我重申这一点，因为实际上其他列都来自同一表，并且mainproduk.id是mainproduk表的主键，在这种情况下，您所做的是有效的。